Help with Pivoting columns

[PowerBIKaizen]

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!

[v-jingzhan-msft · Accepted solution]

Hi @PowerBIKaizen 

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. 

• Duplicate the original table;
• Keep only the first row which has types;
• Select the first column "Week Ending" and unpivot all other columns;
• Extract text before "." in "Attribute" column;
• Keep only "Attribute" and "Value" columns and rename them. 

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!

[PowerBIKaizen]

Thank you! This got me to what I needed. Having the second table to merge on was the key info I needed. 

[lbendlin]

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...