Unstacking Columns in Power Query

M

mschultz23

New member
Joined
Jul 13, 2016
Messages
6
Reaction score
0
Points
0
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:

EntryDimensionCode
1DivisionNorth America
1DepartmentMarketing
1Project1000
2DivisionNorth America
2DepartmentFinance
2Project2000


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


EntryDivisionDepartmentProject
1North AmericaMarketing1000
2North AmericaFinance2000

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.


EntryDivisionDepartmentProject
1North AmericaMarketingnull
1North Americanull1000
2North AmericaFinancenull
2North Americanull2000

I'm looking for the easiest way to consolidate. Thank you for any help!
 
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.
 
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.

EntryDivisionDepartmentProject
1North AmericaMarketing1000
2North AmericaFinance2000


Your help is appreciated!
 
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:

Code: 
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"
 

Attachments

  • Unstacking_Columns_in_Power_Query.xlsx
    18.1 KB · Views: 56
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!
 
You're welcome! There might be a way to include the other columns if you needed them, but if not, good riddance.
 
You must log in or register to reply here.
Back
Top