Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Special Transpose to get output

Hi , I need to transpose a dataset to get a specific value.

Here is my Input table

Below is my output table.

 

For each ticket in the input table I need to get the key values as columns and its corresponding values as values in the table. Please note that I cannot directly use a transpose as the dataset is very huge.

Please help me with the above request and let me know if any more info is required or need further explanation.

 

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Anonymous Can you just Pivot the Key column?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcnQEEoZKsTpwAScgUZKRiizkDFJjZAwVMoJpMzJBFgHpS0xKRhaC6IOKGMNtQxEBaTMyNkEWAmlzdHFGFnIBEmUpSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tickets = _t, Key = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tickets", Int64.Type}, {"Key", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Key]), "Key", "Value")
in
    #"Pivoted Column"

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Greg_Deckler I forgot to check that dont aggregate option. Thanks it works now.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Anonymous Can you just Pivot the Key column?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcnQEEoZKsTpwAScgUZKRiizkDFJjZAwVMoJpMzJBFgHpS0xKRhaC6IOKGMNtQxEBaTMyNkEWAmlzdHFGFnIBEmUpSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tickets = _t, Key = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tickets", Int64.Type}, {"Key", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Key]), "Key", "Value")
in
    #"Pivoted Column"
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Greg_Deckler  I have tried this but I'm not getting the desired output as my dataset is huge, I'm just getting 0 and 1 in my output. The method of pivoting key field would work for small use cases. What do I do