Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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"
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! |
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"
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
15 | |
14 | |
12 |