power query how to make formulas to auto count greater than and less than value
- Thread starter Oscarr
- Start date
rahulshewale1
Active Member
hi,
See if is ok ?
See if is ok ?
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Name", type text}, {"company", type text}, {"product", type text}, {"sell", type number}, {"profit", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [sell] >= 1000 and [sell] <= 2000 then "1000 TO 2000"
else if [sell] >= 2001 and [sell] <= 3000 then "2000 TO 3000"
else if [sell] >= 3001 and [sell] <= 4000 then "3000 TO 4000"
else if [sell] >= 4001 and [sell] <= 5000 then "4000 TO 5000"
else if [sell] >= 5001 and [sell] <= 6000 then "5000 TO 6000"
else if [sell] >= 6001 and [sell] <= 7000 then "6000 TO 7000"
else if [sell] >= 7001 and [sell] <= 8000 then "7000 TO 8000"
else if [sell] >= 8001 and [sell] <= 9000 then "8000 TO 9000"
else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"date", "Name", "Custom", "company", "product", "sell", "profit"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Name", "product"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"date", "Custom", "company"}, {{"Total Sell", each List.Sum([sell]), type number}, {"Total Profit", each List.Sum([profit]), type number}, {"Count", each Table.RowCount(_), type number}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Grouped Rows",{"date", "Custom", "company", "Count", "Total Sell", "Total Profit"})
in
#"Reordered Columns1"rahulshewale1
Active Member
hi @rahulshewale1
still got 1 problem, the custom column can be A-Z?
example:
company A
1-100
101-200
2001-300
company B
1-100
101-200
2001-300
rahulshewale1
Active Member
hi @Oscarr ,
Follow below steps
1) Added Index : Go to Add Column tab > Index Column > Select From 1
2) Sorted Rows: Sort Index Column (A To Z)
3) BufferedTable: Click fx Option (Near Formula Bar) > Then type formula = Table.Buffer(#"Sorted Rows")
rahulshewale1
Active Member
hi @Oscarr ,
Use For Sorting >>>
if [Sale Between]= "1 TO 100" then 1
else if [Sale Between]= "101 TO 200" then 2
So on ...........
Else null
Then sort a to z
hi @rahulshewale1
can make that sample file for me? Because not so understand how to make it.
pls view attach pic...
Last edited:
