Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combining rows and aggregating data at the same time

Hi Friends ,

 

I need some help,

 

Current State of my Data 

 

CategoryAttributeConsumption_DateValue
Toilet PaperOn Stock5/29/2020 12:00:00 AM15.62
Toilet PaperOn Order5/29/2020 12:00:00 AM0.00
GlovesOn Stock5/29/2020 12:00:00 AM0.64
GlovesOn Order5/29/2020 12:00:00 AM10.88
Paper TowelsOn Stock5/29/2020 12:00:00 AM4.81
Paper TowelsOn Order5/29/2020 12:00:00 AM1.92
N95 MaskOn Stock5/29/2020 12:00:00 AM6.37
N95 MaskOn Order5/29/2020 12:00:00 AM0.00
Hand SanitizerOn Stock5/29/2020 12:00:00 AM4.73
Hand SanitizerOn Order5/29/2020 12:00:00 AM1.35
WipesOn Stock5/29/2020 12:00:00 AM1.38
WipesOn Order5/29/2020 12:00:00 AM2.10
Hand SoapOn Stock5/29/2020 12:00:00 AM1.12
Hand SoapOn Order5/29/2020 12:00:00 AM1.34
Disinfectant Spray / General DisinfectantOn Stock5/29/2020 12:00:00 AM4.04
Disinfectant Spray / General DisinfectantOn Order5/29/2020 12:00:00 AM17.95
3-Ply MaskOn Stock5/29/2020 12:00:00 AM6.16
3-Ply MaskOn Order5/29/2020 12:00:00 AM20.35

 

 

Desired State: in Power Query 

 

CategoryAttributeDateValue
Toilet PaperOn stock : On Order5/29/2020 12:00:00 AM15.62
GlovesOn stock : On Order5/29/2020 12:00:00 AM11.52
Paper TowelsOnstock : On Order5/29/2020 12:00:00 AM6.73
N95 MaskOnstock : On Order5/29/2020 12:00:00 AM6.37
Hand SantizerOnstock : On Order5/29/2020 12:00:00 AM6.08
WipesOnstock : On Order5/29/2020 12:00:00 AM3.48
Hand SoapOnstock : On Order5/29/2020 12:00:00 AM2.47
Disinfectant Spray / General DisinfectantOnstock : On Order5/29/2020 12:00:00 AM21.99
3-Ply MaskOnstock : On Order5/29/2020 12:00:00 AM26.51

 

 

Can someone help me how to achieve this ASAP

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Attribute", type text}, {"Consumption_Date", type datetime}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Attribute.1", each "On Stock : On Order"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Category", "Consumption_Date", "Attribute.1"}, {{"Value", each List.Sum([Value]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Attribute.1", "Attribute"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Category", "Attribute", "Consumption_Date", "Value"})
in
    #"Reordered Columns"

    Hope this helps.

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    Just try this simple way as below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLNasMwEIRfZfE5Wa/k/94KheSSJuBADyEHkaggIiRji5b06WvkS2htpBZ0WCQ+zc7Onk7J0SotHRxEJ/tklewNtM5ebmNZpLxJOXECxp+IxgPPu/GeFVjy5LyaY/f91ZdLLCGRRzfafsghTpCwzH9BISVGWNee8u3B0X5KHSmYY80W0KAsNtNsXpsCdmK4xSmWmFUzWPQ4t8JcoRVGOfUVm2OOVbYIh31mhYffVBeb48jUP5mQDkf26NCKLlaL8Tkuwte0ay9qUOZdXpwwDtquF3dIYSON7IWGx8fYYdO//g32W2EzBZGtD/r+p5Vj5SwYjIR89udv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Attribute = _t, Consumption_Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Attribute", type text}, {"Consumption_Date", type datetime}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Consumption_Date"}, {{"Value", each List.Sum([Value]), type number}, {"AllData", each _, type table [Category=text, Attribute=text, Consumption_Date=datetime, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Attribute", each [AllData][Attribute]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Attribute", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"AllData"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Category", "Consumption_Date", "Attribute", "Value"})
in
    #"Reordered Columns"

    Result:

     

    here is a similar post, you could refer to it too:

    https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

     

    and here is my sample pbix file, please try it.

     

    Regards,

    Lin

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , What I observer is MAX Attribute and consumption date and sum of value in a Table or Matrix

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      I need to do 2 things here

       

      1. Aggregate on the Category and at the same time combine the attribute into 1 column? do you think I can achieve both with Max?

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

         

        New Measure = max(Table[Category]) & " : " & max(Table[Date])

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Please descibe the conditions very clearly.  Will there always be only 2 rows per Category?  What is the relevance of On Stock : On order?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Ashish_Mathur wrote:

      Hi,

      Please descibe the conditions very clearly.  Will there always be only 2 rows per Category?  What is the relevance of On Stock : On order?

      Ashish_Mathur  yes there will be always 2 rows per category, 

       

      I have to combine 2 rows ON Stock & On Order in to a New column ( where it shows as On Stock: On Order  + at the same time the Value Column has to be aggregated 

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Anonymous 

    Just try this simple way as below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLNasMwEIRfZfE5Wa/k/94KheSSJuBADyEHkaggIiRji5b06WvkS2htpBZ0WCQ+zc7Onk7J0SotHRxEJ/tklewNtM5ebmNZpLxJOXECxp+IxgPPu/GeFVjy5LyaY/f91ZdLLCGRRzfafsghTpCwzH9BISVGWNee8u3B0X5KHSmYY80W0KAsNtNsXpsCdmK4xSmWmFUzWPQ4t8JcoRVGOfUVm2OOVbYIh31mhYffVBeb48jUP5mQDkf26NCKLlaL8Tkuwte0ay9qUOZdXpwwDtquF3dIYSON7IWGx8fYYdO//g32W2EzBZGtD/r+p5Vj5SwYjIR89udv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Attribute = _t, Consumption_Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Attribute", type text}, {"Consumption_Date", type datetime}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Consumption_Date"}, {{"Value", each List.Sum([Value]), type number}, {"AllData", each _, type table [Category=text, Attribute=text, Consumption_Date=datetime, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Attribute", each [AllData][Attribute]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Attribute", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"AllData"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Category", "Consumption_Date", "Attribute", "Value"})
in
    #"Reordered Columns"

    Result:

     

    here is a similar post, you could refer to it too:

    https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

     

    and here is my sample pbix file, please try it.

     

    Regards,

    Lin