PDA

View Full Version : Unstacking Columns in Power Query



mschultz23
2016-07-13, 07:40 PM
One of the data tables I'm using has several different categories stacked on top of each other rather than as separate columns (the way they show in most other tables here)

Below is a simplified version of the table that I need to transform:



Entry
Dimension
Code


1
Division
North America



1
Department
Marketing


1
Project
1000


2
Division
North America


2
Department
Finance


2
Project
2000




Ideally, I'd be able to get it to look like the table below:




Entry
Division
Department
Project


1
North America
Marketing
1000


2
North America
Finance
2000



If I'm able to split out that Dimension column, I'll be able to filter by Department and Project to minimize the data that I'm bringing into my report. So far, I've been able to pivot on the Dimension column to get a table that looks like this.




Entry
Division
Department
Project


1
North America
Marketing
null


1
North America
null
1000


2
North America
Finance
null


2
North America
null
2000



I'm looking for the easiest way to consolidate. Thank you for any help!

SteelReyn
2016-07-14, 03:53 PM
You were close! I got it down to one Pivot step after some tinkering. If you import your initial table, then select the Dimension column and Pivot it, use Code in the Values Column. Open the Advanced options and select Don't Aggregate for the Aggregate Value Function. This produced the exact end result you're looking for.

mschultz23
2016-07-14, 06:05 PM
Thanks for taking a look at this for me! I should have organized my initial post better. The last table in my post is where I currently was using the same steps you provided.

My next step needs to be consolidating those rows so that each entry number shows only once as displayed in the middle table of my post. I basically need to merge the rows to eliminate the "nulls". I've been looking up a lot of transformation videos, but can't find exactly what I'm looking for or what to call it.



Entry
Division
Department
Project


1
North America
Marketing
1000


2
North America
Finance
2000




Your help is appreciated!

SteelReyn
2016-07-14, 08:33 PM
Your post was fine, but maybe my response wasn't. When I do the steps I listed, I end up with the table you want, with only 1 row per Entry. There were no nulls or extra rows to figure out how to condense. I go straight from the table with three rows per Entry to the table with 1 row per Entry. See attached, or M code below:


let
Source = Excel.CurrentWorkbook(){[Name="InputTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry", Int64.Type}, {"Dimension", type text}, {"Code", type any}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Dimension]), "Dimension", "Code")
in
#"Pivoted Column"

mschultz23
2016-07-15, 07:13 PM
Thank you so much for taking the time to help me out. After rereading your second post, I realized what my issue was. In my actual data, I had two other columns that had been imported that I wasn't actually using. By leaving them in there, it was messing up the output. Once I narrowed it down to be like my example, the steps worked perfectly.

Thanks!

SteelReyn
2016-07-15, 07:39 PM
You're welcome! There might be a way to include the other columns if you needed them, but if not, good riddance.