My last post was about an awkward dataset. Now I’ll review Daniel Choi’s Power Query solution that rearranges the data.

 

Recap

To answer a question do we leave the data as is and use complex formulas or rearrange it so we can use easier formulas?

 

Awkward Data

Below we see the awkward data we’ve been given.

 

 

 

 

 

My last post used formulas to answer a question directly on this data. I also rearranged it using formulas.

 

Power Query Solution!

Daniel Choi sent me his amazing power query solution (Excel file)! How does it work?

 

Open Power Query

Daniel created a named range called RawData. He loaded this range into power query.

How do we get there? On the ribbon click Data / Queries & Connections

 

 

 

 

Hover over RawData and click edit. This should open power query.

 

Query Settings

Below we see Daniel’s 11 transformation steps. Looks like he created most (all?) steps using menu choices. Each step creates M code which I’ve included in blue.

 

 

 

 

 

 

 

 

 

 

Source

= Excel.CurrentWorkbook(){[Name=”RawData”]}[Content]

He loaded named range RawData from the current workbook. It should be easy to swap RawData with another named range if necessary at a later date.

 

Transposed Table

#”Transposed Table” = Table.Transpose(Source)

Rows become columns. Click between steps Source and Transposed Table until you see how it works.

 

 

 

 

 

 

 

Filled Down

= Table.FillDown(#”Transposed Table”,{“Column1”})

In Column1 we see the month names are filled down.

 

 

 

 

 

 

 

 

Table.Split

= Table.Split(#”Filled Down”,3)

***This is an amazing step! I had never seen Table.split before!***

It splits the dataset into 3 tables (3 rows each). Is there a way to make the 3 a variable based on the number of months? Could we use a power query function?

 

 

 

 

 

Converted to Table

= Table.FromList(TableSplit, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

List above with 3 tables is converted into a table (1 column, each row is a table) below.

 

 

 

 

 

Transpose

= Table.AddColumn(#”Converted to Table”, “Custom”, (OT)=> Table.AddColumn( Table.Transpose(Table.RemoveColumns(OT[Column1],{“Column1″})),”Month”, (IT)=> OT[Column1][Column1]{0}) )

I don’t understand what this step is doing. Hopefully over time I’ll figure it out.

 

 

 

 

 

Removed Columns

= Table.RemoveColumns(Transpose,{“Column1”})

This step is clear. Daniel created the Custom column. Column 1 is no longer needed.

 

 

 

 

 

Expanded Custom

= Table.ExpandTableColumn(#”Removed Columns”, “Custom”, {“Column1”, “Column2”, “Column3”, “Month”}, {“Column1”, “Column2”, “Column3”, “Month”})

If you’ve done a merge in power query you’ll be familiar with this. Click the double arrows, seen in pic above, to split out into columns. Now you see this:

 

 

 

 

 

 

Everything (Category, Letter, Value, Month) has it’s own column. This is the layout you need so you can:

• use simple formulas
• make a pivot
• upload data into a database

 

Renamed Columns

= Table.RenameColumns(#”Expanded Custom”,{{“Column1”, “Category”}, {“Column2”, “Letter”}, {“Column3”, “Value”}})

Each set (eg {“Column1”, “Category”}) contains the current column name followed by the new name.

 

Reordered Columns

= Table.ReorderColumns(#”Renamed Columns”,{“Month”, “Category”, “Letter”, “Value”})

Simple step of reordering the columns.

 

Changed Type

= Table.TransformColumnTypes(#”Reordered Columns”,{{“Month”, type text}, {“Category”, type text}, {“Letter”, type text}, {“Value”, type number}})

The last step changes the datatypes of the columns (or maybe it simply confirms the datatypes).

 

Export Back to Sheet

In the top left corner you see the Close & Load button. After the final transformation step above (Changed Type) he sent the rearranged data back to sheet Power Query.

 

 

Advanced Editor

With the rearranged data sent back to a sheet we’re done but let’s look at the complete M code back in power query. On the ribbon click Home / Advanced Editor. Here it is:

let
Source = Excel.CurrentWorkbook(){[Name=”RawData”]}[Content],
#”Transposed Table” = Table.Transpose(Source),
#”Filled Down” = Table.FillDown(#”Transposed Table”,{“Column1”}),
TableSplit = Table.Split(#”Filled Down”,3),
#”Converted to Table” = Table.FromList(TableSplit, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Transpose = Table.AddColumn(#”Converted to Table”, “Custom”, (OT)=> Table.AddColumn( Table.Transpose(Table.RemoveColumns(OT[Column1],{“Column1″})),”Month”, (IT)=> OT[Column1][Column1]{0}) ),
#”Removed Columns” = Table.RemoveColumns(Transpose,{“Column1”}),
#”Expanded Custom” = Table.ExpandTableColumn(#”Removed Columns”, “Custom”, {“Column1”, “Column2”, “Column3”, “Month”}, {“Column1”, “Column2”, “Column3”, “Month”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded Custom”,{{“Column1”, “Category”}, {“Column2”, “Letter”}, {“Column3”, “Value”}}),
#”Reordered Columns” = Table.ReorderColumns(#”Renamed Columns”,{“Month”, “Category”, “Letter”, “Value”}),
#”Changed Type” = Table.TransformColumnTypes(#”Reordered Columns”,{{“Month”, type text}, {“Category”, type text}, {“Letter”, type text}, {“Value”, type number}})
in
#”Changed Type”

Notice that each step references the previous step. Power Query gurus can modify this menu generated code or even write it directly without using the menus! I’ve tinkered around with it a couple of times. I’ve still got so much to learn.

 

 

Daniel Choi

Thanks again Daniel for sending me your solutions! Daniel shared some of his favorite power query links:

Key Concept: Split Function:

https://www.poweredsolutions.co/2019/12/19/split-segment-partition-section-your-table-in-power-bi-power-query/

 

Bonus Solution

Daniel just sent me a shorter 7 step solution! Oddly, I can’t upload his zip file but here is his M code:

let
Source = Excel.CurrentWorkbook(){[Name=”RawData”]}[Content],
#”Transposed Table” = Table.Transpose(Source),
#”Filled Down” = Table.FillDown(#”Transposed Table”,{“Column1”}),
#”Grouped Rows” = Table.Group(#”Filled Down”, {“Column1”}, {{“All”, each Table.Transpose(Table.RemoveColumns(_,{“Column1”})), type table}}),
#”Expanded All” = Table.ExpandTableColumn(#”Grouped Rows”, “All”, {“Column1”, “Column2”, “Column3”}, {“Column1.1”, “Column2”, “Column3”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded All”,{{“Column1”, “Month”}, {“Column1.1”, “Category”}, {“Column2”, “Letter”}, {“Column3”, “Value”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Month”, type text}, {“Category”, type text}, {“Letter”, type text}, {“Value”, type number}})
in
#”Changed Type”

 

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada (outside of Toronto).

During this pandemic I’m trying to keep my mind busy. I guess it’s good that I’ve still got so much to learn about Excel and Power BI. Both include Power Query so that’s a bonus.