Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello, I'm trying to pivot my data into tabular format using PowerQuery.
This is what my data looks like:
I'd like to have two seperate columns for attributes such as "Food & Beverage" and the other column contains item type of "INCs, RITMS) see below target state:
Thank you!
Solved! Go to Solution.
I guess there are probably multiple Areas in the table, and under each Area there are many types just like below table. If your table is like this, you can try my approach listed below. Hope this would be helpful.
1. Prepare a table containing Areas and Types.
You will have a table like this. We will then merge it to the original table later.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8vRzDgZSQZ4hvkAqIL8ksSQfyAjJzwUxYnWilQwN9M31jQyMjIHCZgZAwgKIDUEMU1OYAkMTmAoLc5AESIURSL0ZTIWRIVwFSNYYqsrERCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week Ending" = _t, #"Food & Beverage" = _t, #"Food & Beverage.1" = _t, #"Fruit & Vegetable" = _t, #"Fruit & Vegetable.1" = _t]),
#"Kept First Rows" = Table.FirstN(Source,1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Kept First Rows", {"Week Ending"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Extracted Text Before Delimiter",{"Attribute", "Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute", "Area"}, {"Value", "Item Type"}})
in
#"Renamed Columns"
2. Transform the original table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8vRzDgZSQZ4hvkAqIL8ksSQfyAjJzwUxYnWilQwN9M31jQyMjIHCZgZAwgKIDUEMU1OYAkMTmAoLc5AESIURSL0ZTIWRIVwFSNYYqsrERCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week Ending" = _t, #"Food & Beverage" = _t, #"Food & Beverage.1" = _t, #"Fruit & Vegetable" = _t, #"Fruit & Vegetable.1" = _t]),
// Start transforming table from here
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"", "Week Ending"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week Ending", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Week Ending", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Week Ending", "Index"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Item Type"}, {"Value", "Item Value"}, {"Index", "Period"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Item Type"}, #"Item Types", {"Item Type"}, "Item Types", JoinKind.LeftOuter),
#"Expanded Item Types" = Table.ExpandTableColumn(#"Merged Queries", "Item Types", {"Area"}, {"Area"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Item Types",{"Area", "Item Type", "Item Value", "Week Ending", "Period"})
in
#"Reordered Columns"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
I guess there are probably multiple Areas in the table, and under each Area there are many types just like below table. If your table is like this, you can try my approach listed below. Hope this would be helpful.
1. Prepare a table containing Areas and Types.
You will have a table like this. We will then merge it to the original table later.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8vRzDgZSQZ4hvkAqIL8ksSQfyAjJzwUxYnWilQwN9M31jQyMjIHCZgZAwgKIDUEMU1OYAkMTmAoLc5AESIURSL0ZTIWRIVwFSNYYqsrERCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week Ending" = _t, #"Food & Beverage" = _t, #"Food & Beverage.1" = _t, #"Fruit & Vegetable" = _t, #"Fruit & Vegetable.1" = _t]),
#"Kept First Rows" = Table.FirstN(Source,1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Kept First Rows", {"Week Ending"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Extracted Text Before Delimiter",{"Attribute", "Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute", "Area"}, {"Value", "Item Type"}})
in
#"Renamed Columns"
2. Transform the original table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8vRzDgZSQZ4hvkAqIL8ksSQfyAjJzwUxYnWilQwN9M31jQyMjIHCZgZAwgKIDUEMU1OYAkMTmAoLc5AESIURSL0ZTIWRIVwFSNYYqsrERCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week Ending" = _t, #"Food & Beverage" = _t, #"Food & Beverage.1" = _t, #"Fruit & Vegetable" = _t, #"Fruit & Vegetable.1" = _t]),
// Start transforming table from here
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"", "Week Ending"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week Ending", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Week Ending", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Week Ending", "Index"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Item Type"}, {"Value", "Item Value"}, {"Index", "Period"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Item Type"}, #"Item Types", {"Item Type"}, "Item Types", JoinKind.LeftOuter),
#"Expanded Item Types" = Table.ExpandTableColumn(#"Merged Queries", "Item Types", {"Area"}, {"Area"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Item Types",{"Area", "Item Type", "Item Value", "Week Ending", "Period"})
in
#"Reordered Columns"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Thank you! This got me to what I needed. Having the second table to merge on was the key info I needed.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |