• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

power query how to make formulas to auto count greater than and less than value

Oscarr

Member
hi all

how to use power query make a formulas to auto count greater than and less than value?

thanks
 

Attachments

  • sample.xlsx
    11.6 KB · Views: 6
hi,

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"
 

Attachments

  • sample (1).xlsx
    27 KB · Views: 4
hi,

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"

hi @rahulshewale1

thanks for help....

is it posible to make same company only show 1?
 

Attachments

  • sample.jpg
    128.4 KB · Views: 6
hi,

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"

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
 

Attachments

  • sample2.jpg
    98.3 KB · Views: 5
Back
Top