Unpivoting multiple (or combination of) columns of data

[Linksys45]

Hey everyone,

New to BI, so bare with me. I am working in Power Query, but am really having trouble untangaling data and how it is formatted.

Here is the example of the current state:

Comapny Name

Initiative 1

Description for Initiative 1

Goal for Initiative 1

Scope for Initiative 1

Challenges for Initiative 1

Initiative 2

Description for Initiative 2

Goal for Initiative 2

Scope for Initiative 2

Challenges for Initiative 2

Initiative 3

same columns as past two

Company 1

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Company 2

Company 3

I don't know how to untangle it from being seperate columns to just rows like this (I'm guess some unpivoting but I've been unsuccessful so far). I want it to look like this:

Comapny Name	Initiative	Description for Initiative	Goal for Initiative	Scope for Initiative	Challenges for Initiative
Company 1	Initiative 1	xxx	xxx	xxx	xxx
Company 1	Initiative 2
Company 1	Initiative 3
Company 2	Initiative 1
Company 2	Initiative 2
Company 2	Initiative 3
Company 3	Initiative 1
Company 3	Initiative 2
Company 3	Initiative 3

I've tried unpivoting only selected columns on each column of "Initiative X" column, but that only gets it half way as I am still stuck with all the value columns staying where they are with 3 repeating rows with only the initiaive column being different, so it  reads like this:

Comapny Name	Description for Initiative 1	Goal for Initiative 1	Scope for Initiative 1	Challenges for Initiative 1	Description for Initiative 2	Goal for Initiative 2	Scope for Initiative 2	Challenges for Initiative 2	Initiative
Company 1	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	Intiative 1
Company 1	This	is	all the	same	data one	row	above	& Below	Intiative 2
Company 1	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	Initiative 3
Company 2	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	Intiative 1
Company 2	This	is	all the	same	data one	row	above	& Below	Intiative 2
Company 2	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	Initiative 3

Is there any good way to do this?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comapny Name", type text}, {"Initiative 1", type text}, {"Description for Initiative 1", type text}, {"Goal for Initiative 1", type text}, {"Scope for Initiative 1", type text}, {"Challenges for Initiative 1", type text}, {"Initiative 2", type text}, {"Description for Initiative 2", type text}, {"Goal for Initiative 2", type text}, {"Scope for Initiative 2", type text}, {"Challenges for Initiative 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Comapny Name"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"

Hope this helps.

[Linksys45]

This worked exactly as I was looking for! Thank you :)!

[Ashish_Mathur]

You are welcome.

[v-janeyg-msft]

Hi, @Linksys45 

According to your description, I made some fake data and unpivot columns in PQ.

Like this:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Linksys45 , This blog talks about unpivot twice

https://kohera.be/blog/power-bi/how-to-unpivot-twice/