Covert multiple Column Groups to Rows

Power Query M Code Showcase
1

Hi everyone,

I’m facing a difficult problem when transforming data.
My raw data structure likes below. And every six months, data will be filled in a different sheet.

  • I want to merge all data into one table
  • Is there any way to collect the year in the sheet name?
  • Convert data into an accessible way to calculate and visualize in PBI.
    image
    image973×178 3.47 KB

Here is my expected result
image
PowerQuery_Example.xlsx (9.9 KB)

Can anyone help me?
Thank you

P/s: I found a video of Goodly resolving the scenario a little bit like my problem. But I don’t know how to apply it to my case
https://youtu.be/e0Rn8HRr-0M

2

Hey Collier,

This is a fun setup. You can shape the date exactly the way you like with the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content],
    Transpose = Table.Transpose(Source),
    FillDown = Table.FillDown(Transpose,{"Column1"}),
    PromoteHeaders = Table.PromoteHeaders(FillDown, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(PromoteHeaders,{{"Column1", "Period"}}),
    Add_SortColumn = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type),
    Adj_IndexColumn = Table.TransformColumns(Add_SortColumn, {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Adj_IndexColumn, {"Period", "Team", "Index"}, "Attribute", "Value"),
    PivotCols = Table.Pivot(Unpivot, List.Distinct(Unpivot[Team]), "Team", "Value", List.Sum),
    SortRows = Table.Sort(PivotCols,{{"Index", Order.Ascending}, {"Attribute", Order.Ascending}}),
    RemoveOtherCols = Table.SelectColumns(SortRows,{"Period", "Attribute", "Plan", "Actual", "Gap"}),
    ChangeType = Table.TransformColumnTypes(RemoveOtherCols,{{"Plan", Int64.Type}, {"Actual", Int64.Type}, {"Gap", Int64.Type}, {"Period", type text}, {"Attribute", type text}})
in
    ChangeType

To make sure the sorting remains as desired, you can include an index column. And then use the Number.IntegerDivide operation to return the relevant numbers, as described here: https://powerquery.how/number-integerdivide/

Here’s a copy of the solution file too.

PowerQuery_Example.xlsx (19.0 KB)

Hope that helps!

2 Likes
3

Thanks @RickdeGroot, this helps me a lot.

However, my file includes many sheets and each of them contains data of half year (6 months). Should I merge the sheets in my file before transforming the data, or should I do it after? Additionally, how can I extract the year from the sheet name and concatenate it with the corresponding month?

Once again, thanks for your help.

4

Hi @collier.brittany

I have written a custom M function for combine N top rows with specific delimiter which can help whenever you have a challenge like this and even if you have more that two rows.

Please find attached file.

Below is the link to my Custom M function:

PowerQuery_Example (1).xlsx (21.6 KB)

Regards

5

Thank you @SoftwareTrain.

One of the reasons I enjoy knowledge sharing is because it provides an opportunity to learn something new from others every day. That is why Pay It Forward is so important to me.

I do encourage others who are reading this post to visit Soheil Bakhshi’s bi-insight blog where he showcases this function in the blogpost Combining X Number of Rows in Power Query for Power BI, Excel and Power Query Online

6

Thanks @SoftwareTrain and @ystroman

1 Like