Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Transform and Sort Data from Buckets and Data in same Column

Hello,   I'm new here and have not been able to find a similar issue to the one I am encountering.    My data is currently organized this way in an autogenerated report. The buckets are in the sa...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNzk4tUXBU0lHKK83JUYrViVZydHIGcs0NkDmWuGQsTMEcqDlOmOaYGCFxjJC1mqFodcbUamiCxLFENscYmWNqjKzMGM2AWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Expense = _t, #"Expense Amount" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Expense", type text}, {"Expense Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Expense Amount]=null then [Expense] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Expense"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Expense Amount] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Temp", each _, type table [Expense Amount=nullable number, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Temp],"Index")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Temp"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.1", {"Expense Amount", "Index"}, {"Expense Amount", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom.1", List.Distinct(#"Expanded Custom.1"[Custom]), "Custom", "Expense Amount", List.Sum),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns2"

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

Simple enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNzk4tUXBU0lFSitWJVnJ0cgYyzQ2QOZa4ZCxMwRyoGU6oZpgYIXGMkLWZoWhzRtVmaILEsUQ2wxiZY2qMrMwYzYBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Expense = _t, #"Expense Amount" = _t]),

    Grouped = let cols=Table.ColumnNames(Source) in Table.Group(Source, "Expense Amount", {"grp", each Table.CombineColumns(_, cols, (l)=>if l{1}="" then l{0} else l{1}, "Bucket")[Bucket]}, 0, (x,y) => Byte.From(y="")),
    Result = Table.PromoteHeaders(Table.FromColumns(Grouped[grp]))
in
    Result