cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abukapsoun Member
Member

Unpivot Table

Hi,

 

I have a bit of strange request not sure if it is possible but I am giving it a try. 

 

I have the following table,

 

 Capture.PNG

 

Is there a way I can duplicate the row, and put technology-2 under technology-1, product-2 under product-1? 

In other way, can I have a single column for " Product", "Technology" and "Skill" and populating tech-1 and tech-2 under it?

So in that case, each single row will have two rows. 

 

thanks,

5 REPLIES 5
Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: Unpivot Table

It is not such a strange request; I have seeen such requests before.

 

I assume you have other columns in your table as well, so I added a "Name" column that represents all those columns.

 

Steps:

  • Select all columns that are not displayed in your picture
  • Choose Transform - Unpivot - Other Columns
  • Select column "Attribute"
  • Choose Transform - Extract - Text Before Delimiter - Delimiter "-", the last delimiter from the end
  • Choose Add Column - Add Index Column
  • Select column "Index"
  • Choose Transform - Standard - Integer-Divide, value 3
  • Select column "Attribute"
  • Choose Transform - Pivot Column - Values column "Value" - advanced option Don't Aggregate
  • Remove column Index

Generated code:

 

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)
abukapsoun Member
Member

Re: Unpivot Table

@MarcelBeug

Thank you very much Marcel for this interesting walkthrough, I have applied the steps but I feel there is something not correct with the alignment.

If I am not mistaken, I haven't seen we have used the index and index/3 columns in the step that follow..

 

Capture.PNG

 

Here is the generated code. 

 

Capture2.PNG

 

Thank you again,

abukapsoun Member
Member

Re: Unpivot Table

@MarcelBeug

 

Hi Marcel, 

 

Sorry for bothering again, but were you able to have a look at the below? 

 

Thanks again

MarcelBeug Super Contributor
Super Contributor

Re: Unpivot Table

Apologies for the late reaction.

 

You made one mistake: you added a column with the Integer-Divide, you should have Integer-Divided the Index column on the "Transform Column" tab, thus transforming the Index column in the Integer-Divided column.

 

If you corect that, it should be OK.

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Unpivot Table

On second thought, it may not work as I think you have null values.

 

An alternative approach is to unpivot the other columns and split the Attribute column on "-".

Then you can pivot back on column "Attribute,1"  and remove "Attribute,2" afterwards,

 

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,245)