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
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 143 members 1,848 guests
Please welcome our newest community members: