Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

pivot and summarize new table

Hi all, 

I have a table like this : 

 

 

And I'd like to create a new table such as : 

 

Any ideas? 

thanks in advance! 

Ana 

Need Help

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Opem edit queries, click "advanced editor" to see code applied.

    You could also click on the "APPLIED STEP" to see which transform each step do.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5NykpNLjFU0lEyNAUSRgZgAkQqxerA5Y1AfB0lY5ASQwxZY5CoOUy3hQHYMANkFSZQIbApUBuQpOE2w2kkSTOIuAVU3hCoIDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, transport = _t, fod = _t, accomodation = _t, other = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"transport", Int64.Type}, {"fod", Int64.Type}, {"accomodation", Int64.Type}, {"other", Int64.Type}}),
    new2 = Table.InsertRows(#"Changed Type",6,  { [id="amount", transport=List.Sum(#"Changed Type"[transport]), fod=List.Sum(#"Changed Type"[fod]),accomodation=List.Sum(#"Changed Type"[accomodation]), other=List.Sum(#"Changed Type"[other])] }),
    new3=Table.InsertRows(new2,7,  { [id="#", transport= List.NonNullCount (new2[transport])-1, fod= List.NonNullCount (new2[fod])-1,accomodation= List.NonNullCount (new2[accomodation])-1, other= List.NonNullCount (new2[other])-1] }),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(new3, {"id"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each not Text.Contains([id], "subject")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[id]), "id", "Value")
in
    #"Pivoted Column"

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie, 

      thanks for your reply, but I'm afraid it wont work, since I have not 6 subjects, but more than 30.000, and this number increases every day. I'm afraid your solution will be too much memory consuming. I guess I have to proceed by steps : 

      1) create a new table with Col1 = distinctcount of subjects, Col2 = total sum of transport, Col3 = total sum of food and so on

      2) unpivot the new table. 

       

      For step 1, I tried with summarize function, but since I don't want to group by, it doest work. Any idea on how to create this table 1)? 

       

      Thanks in advance! 

      Ana