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,

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)