Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
dpwhitey1
Frequent Visitor

How to pivot Multiple Columns

‎03-10-2023 06:00 AM

Hello

 

Is there an easy way to use Power Query to pivot data with a Unique ID spread over multiple rows, into a spreadsheet with one row per ID?

eg I have 22 milestones below, each milestone has 7 values columns.

How do I pivot the milestone column 7 times to create 154 columns in each unique ID row?

 

dpwhitey1_0-1678454867349.png

 

eg I need it to display like this, as this data needs to be merged with an existing legacy excel report

 

dpwhitey1_1-1678455766500.png

 

Do I need to duplicate the milestone column 7 times, then pivot each milestone duplicate against each value column?

If so is there an easy way to rename each new column to "Milestone 0 Active", "Milestone 1 Active" etc without having to manually rename 154 columns?

 

dpwhitey1_2-1678456119452.png

 

 

Thanks very much

Labels:
Message 1 of 7
13,305 Views
0
1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User
In response to dpwhitey1

‎03-12-2023 02:02 PM

Assuming all of the ID's have all of the Milestone's listed in proper order, You can:

  • Group by ID
  • For each sub-table
    • Select the ID and Milestone columns and UNPIVOT other columns
    • Merge the Milestone and Attribute column => the new column headers
    • Remove the original columns and more the new merged column to Column2
    • Transpose the resultant table
      • Remove the top row and make the next row the new headers
  • Re-Expand the grouped table

In the code below, change line 2 to reflect your actual data source

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Milestone", type text}, {"Actual Completion Date", type date}, {"Actual Start Date", type date}, {"Active", type text}, {"Activity Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"Transpose", (t)=>
            let 
                #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(t, {"ID", "Milestone"}, "Attribute", "Value"),
                #"Inserted Merged Column" = 
                    Table.AddColumn(
                        #"Unpivoted Other Columns", "Merged", each Text.Combine({[Milestone], [Attribute]}, " - "), type text),
                #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Milestone", "Attribute"}),
                #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Merged", "Value"}),
                #"Transposed Table" = Table.Transpose(#"Reordered Columns"),
                #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
                #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
            in #"Promoted Headers"}}),

    #"Column Names" = Table.ColumnNames(#"Grouped Rows"[Transpose]{0}),
    #"Data Types" = List.Transform(#"Column Names", each if Text.EndsWith(_, "Date") then {_, type date} else {_, type text}), 
    #"Expanded Transpose" = Table.ExpandTableColumn(#"Grouped Rows", "Transpose", #"Column Names"),
    #"Set Data Types" = Table.TransformColumnTypes(#"Expanded Transpose", #"Data Types")
in
    #"Set Data Types"

ronrsnfld_0-1678654944284.png

 

 

 

View solution in original post

Message 4 of 7
13,170 Views
6 REPLIES 6
ronrsnfld
Super User
Super User

‎03-10-2023 10:54 AM

Please provide your data sample as text which can be copy/pasted. Much simpler than trying to work from a screenshot.

Message 2 of 7
13,249 Views
0
dpwhitey1
Frequent Visitor
In response to ronrsnfld

‎03-10-2023 03:05 PM

Here is some sample data

 

IDMilestone Actual Completion DateActual Start DateActiveActivity Status
105610934Milestone 02023-02-222023-02-22NoCompleted
105610934Milestone 1  NoNot Started
105610934Milestone 1.12023-02-26 NoCompleted
105610934Milestone 1.2 2023-02-26YesIn Progress
105610934Milestone 1.3  NoNot Started
105610934Milestone 1.4 2023-02-26YesIn Progress
105610934Milestone 2  NoNot Started
105610934Milestone 2.1  NoNot Started
105610934Milestone 2.2  NoNot Started
105610934Milestone 2.3  NoNot Started
105610934Milestone 2.4  NoNot Started
105610934Milestone 2.5  NoNot Started
105610934Milestone 2.6  NoNot Started
105610934Milestone 3  NoNot Started
105610934Milestone 3.1  NoNot Started
105610934Milestone 3.2  NoNot Started
105610934Milestone 3.3  NoNot Started
105610934Milestone 3.4  NoNot Started
105610934Milestone 3.5  NoNot Started
105610934Milestone 3.6  NoNot Started
105610934Milestone 4  NoNot Started
105610934Milestone 4.1  NoNot Started
105728888Milestone 0 2023-02-23YesIn Progress
105728888Milestone 1  NoNot Started
105728888Milestone 1.1  NoNot Started
105728888Milestone 1.2  NoNot Started
105728888Milestone 1.3  NoNot Started
105728888Milestone 1.4  NoNot Started
105728888Milestone 2  NoNot Started
105728888Milestone 2.1  NoNot Started
105728888Milestone 2.2  NoNot Started
105728888Milestone 2.3  NoNot Started
105728888Milestone 2.4  NoNot Started
105728888Milestone 2.5  NoNot Started
105728888Milestone 2.6  NoNot Started
105728888Milestone 3  NoNot Started
105728888Milestone 3.1  NoNot Started
105728888Milestone 3.2  NoNot Started
105728888Milestone 3.3  NoNot Started
105728888Milestone 3.4  NoNot Started
105728888Milestone 3.5  NoNot Started
105728888Milestone 3.6  NoNot Started
105728888Milestone 4  NoNot Started
105728888Milestone 4.1  NoNot Started
105744750Milestone 02023-02-232023-02-23NoCompleted
105744750Milestone 1  NoNot Started
105744750Milestone 1.12023-02-23 NoCompleted
105744750Milestone 1.22023-02-232023-02-23YesIn Progress
105744750Milestone 1.3  NoNot Started
105744750Milestone 1.42023-02-232023-02-23YesIn Progress
105744750Milestone 2  NoNot Started
105744750Milestone 2.1 2023-02-23YesIn Progress
105744750Milestone 2.2  NoNot Started
105744750Milestone 2.3  NoNot Started
105744750Milestone 2.4  NoNot Started
105744750Milestone 2.5  NoNot Started
105744750Milestone 2.6  NoNot Started
105744750Milestone 3  NoNot Started
105744750Milestone 3.1  NoNot Started
105744750Milestone 3.2  NoNot Started
105744750Milestone 3.3  NoNot Started
105744750Milestone 3.4  NoNot Started
105744750Milestone 3.5  NoNot Started
105744750Milestone 3.6  NoNot Started
105744750Milestone 4  NoNot Started
105744750Milestone 4.1  NoNot Started
105744917Milestone 02023-02-232023-02-23NoCompleted
105744917Milestone 1  NoNot Started
105744917Milestone 1.12023-02-23 NoCompleted
105744917Milestone 1.22023-02-232023-02-23NoCompleted
105744917Milestone 1.3  NoNot Started
105744917Milestone 1.42023-02-232023-02-23NoCompleted
105744917Milestone 2  NoNot Started
105744917Milestone 2.1 2023-02-23YesIn Progress
105744917Milestone 2.2  NoNot Started
105744917Milestone 2.3  NoNot Started
105744917Milestone 2.4  NoNot Started
105744917Milestone 2.5  NoNot Started
105744917Milestone 2.6  NoNot Started
105744917Milestone 3  NoNot Started
105744917Milestone 3.1  NoNot Started
105744917Milestone 3.2  NoNot Started
105744917Milestone 3.3  NoNot Started
105744917Milestone 3.4  NoNot Started
105744917Milestone 3.5  NoNot Started
105744917Milestone 3.6  NoNot Started
105744917Milestone 4  NoNot Started
105744917Milestone 4.1  NoNot Started
Message 3 of 7
13,231 Views
0
ronrsnfld
Super User
Super User
In response to dpwhitey1

‎03-12-2023 02:02 PM

Assuming all of the ID's have all of the Milestone's listed in proper order, You can:

  • Group by ID
  • For each sub-table
    • Select the ID and Milestone columns and UNPIVOT other columns
    • Merge the Milestone and Attribute column => the new column headers
    • Remove the original columns and more the new merged column to Column2
    • Transpose the resultant table
      • Remove the top row and make the next row the new headers
  • Re-Expand the grouped table

In the code below, change line 2 to reflect your actual data source

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Milestone", type text}, {"Actual Completion Date", type date}, {"Actual Start Date", type date}, {"Active", type text}, {"Activity Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"Transpose", (t)=>
            let 
                #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(t, {"ID", "Milestone"}, "Attribute", "Value"),
                #"Inserted Merged Column" = 
                    Table.AddColumn(
                        #"Unpivoted Other Columns", "Merged", each Text.Combine({[Milestone], [Attribute]}, " - "), type text),
                #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Milestone", "Attribute"}),
                #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Merged", "Value"}),
                #"Transposed Table" = Table.Transpose(#"Reordered Columns"),
                #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
                #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
            in #"Promoted Headers"}}),

    #"Column Names" = Table.ColumnNames(#"Grouped Rows"[Transpose]{0}),
    #"Data Types" = List.Transform(#"Column Names", each if Text.EndsWith(_, "Date") then {_, type date} else {_, type text}), 
    #"Expanded Transpose" = Table.ExpandTableColumn(#"Grouped Rows", "Transpose", #"Column Names"),
    #"Set Data Types" = Table.TransformColumnTypes(#"Expanded Transpose", #"Data Types")
in
    #"Set Data Types"

ronrsnfld_0-1678654944284.png

 

 

 

Message 4 of 7
13,171 Views
joelengelhardt
New Member
In response to ronrsnfld

‎08-02-2023 11:53 AM

Thank you for sharing this! For my use for a Word mail merge, I added steps to dynamically use the largest Table to expand the Grouped Rows. This enables using this even if the Pivot value isn't present for each group. Basically, I

  1. Added a Row count aggregate column to the "Grouped Rows" action, alongside the Transpose column you created
  2. Added an Index column
  3. Calculated the Maximum count to determine which table had the MOST rows (that become columns later)
  4. Filtered the Table to show only Rows with a [Count] equal to the max
  5. Removed everything except for the first Row
  6. Drilled down to the Index value of this record
  7. Used THIS INDEX as the key for getting the Column Names
  8. (kept everything after the same)

These steps could certainly be consolidated and shortened to quickly return the Index of the table with ALL columns present.

Here's the code:

 ...

#"Grouped Rows" = Table.Group(#"Merged Columns", {"ID"}, {
{"Transpose", (t)=>
let 
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(t, {"ID", "Milestone"}, "Attribute", "Value"),
#"Inserted Merged Column" = 
Table.AddColumn(
#"Unpivoted Other Columns", "Merged", each Text.Combine({[Milestone], [Attribute]}, ""), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Milestone", "Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Merged", "Value"}),
#"Transposed Table" = Table.Transpose(#"Reordered Columns"),
#"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in #"Promoted Headers"},
{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Calculated Maximum" = List.Max(#"Added Index"[Count]),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Count] = #"Calculated Maximum")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
IndexOfMax = #"Kept First Rows"{[Count=#"Calculated Maximum"]}[Index],
#"Column Names" = Table.ColumnNames(#"Grouped Rows"[Transpose]{IndexOfMax})

...
Message 7 of 7
11,660 Views
0
HS-PA
Regular Visitor
In response to ronrsnfld

‎04-06-2023 09:44 AM

Your solution is amazing, it works wonderful and is easy to follow.  Thank you for sharing.

Message 6 of 7
12,906 Views
dpwhitey1
Frequent Visitor
In response to ronrsnfld

‎03-15-2023 07:13 PM

Thanks very much that worked!

Message 5 of 7
13,099 Views

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

View All
Top Solution Authors
View All
Top Kudoed Authors
View All