Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

 

rasilverio_0-1649887611915.png

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

 

rasilverio_1-1649887717907.png

 

Thank you in advance!

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Vijay_A_Verma
Super User
Super User

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
Super User
Super User

  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

 

ronrsnfld_0-1649892624131.png

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors