Transform and Sort Data from Buckets and Data in same Column

[Anonymous]

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 same column as the data that I need.

Ideally I would like to utilize the PowerQuery options to make those buckets into columns like this. Would any of you have any insight?

Thank you in advance!

[CNENFRNL]

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

[Vijay_A_Verma · Accepted solution]

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"

[ronrsnfld]

1. Transform the Expense column by leaving only the bucket names
   1. These correspond to the null rows in the second column which I have named Expense Amt
2. Fill down the Expense column with the corresponding bucket names
3. Remove the null rows form the second column
4. Pivot with no aggregation
   1. You can't use the Pivot function in the UI because, with no aggregation, it won't work when there are multiple rows of the same type
   2. So I use a custom function (with attribution) found on the net

Custom Pivot function

Rename fnPivotNoAggregation

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotNoAggregation

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Query

let

//Change table name in next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Expense", type text}, {"Expense Amt", Int64.Type}}),

//Remove the non-Bucket Name values from first column
//Then fill down the bucket name
    bucketList = Table.FillDown(
                    Table.FromRecords(
                        Table.TransformRows(#"Changed Type",(r)=>
                            Record.TransformFields(r,{"Expense", each if r[Expense Amt] <> null then null else _}))),
                                {"Expense"}),
//Remove null rows
    #"Filtered Rows" = Table.SelectRows(bucketList, each ([Expense Amt] <> null)),

//Pivot on Expense with no aggregation
    pivot = fnPivotNoAggregation(#"Filtered Rows","Expense","Expense Amt"),

//set data types
    typeIt = Table.TransformColumnTypes(pivot, List.Transform(Table.ColumnNames(pivot),each {_, Int64.Type}))
in
    typeIt