Excel PQ - transform manually created dashboard to suitable for analysis form

I believe many of you have seen such kind of manually created dashboard, usually even more complex

But what can we do with it except showing to stakeholders? If we'd like to make more deep analysis (Month over Month rate, country to country, etc., etc.) and have no access to source data that's only way to create another bunch of rows and columns.

Or transform it for more friendly for data analysis form. That what Power Query is for. Let try to transform into such form.

First, name dashboard area as "range" and query it. Do nothing with it and reference as Blocks query

Just remember that columns are by default named as Column1, Column2, etc.

Keep only first row in this table, transpose it, rename as Country and Fill Down.

Next replace null on "Header" and Add Index column starting from 1, when group by Country column without aggregation

We don't need entire table here, only Index which is actually column numbers for each block. Modify in formula bar as

To have actual column names let add another custom column:

Remove Count column and add customer column with =range formula

We don't need first row here, skip it

and select only related to block columns using Columns

Finally promote headers and add Index column to each table

This query code

let
? ? Source = range,
? ? #"Kept First Rows" = Table.FirstN(Source,1),
? ? #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
? ? #"Renamed Columns" = Table.RenameColumns(
? ? ? ? #"Transposed Table",
? ? ? ? {{"Column1", "Country"}}
? ? ),
? ? #"Filled Down" = Table.FillDown(
? ? ? ? #"Renamed Columns",
? ? ? ? {"Country"}
? ? ),
? ? #"Name for Null" = Table.ReplaceValue(
? ? ? ? #"Filled Down",
? ? ? ? null,"Header",
? ? ? ? Replacer.ReplaceValue,{"Country"}
? ? ),
? ? #"Added Index" = Table.AddIndexColumn(
? ? ? ? #"Name for Null",
? ? ? ? "Index", 1, 1, Int64.Type
? ? ),
? ? #"Grouped Rows" = Table.Group(
? ? ? ? #"Added Index",
? ? ? ? {"Country"}, {{"Count", each _[Index]}}
? ? ),
? ? #"Added Custom" = Table.AddColumn(
? ? ? ? #"Grouped Rows",
? ? ? ? "Columns",
? ? ? ? each List.Transform([Count], each "Column" & Text.From(_))
? ? ),
? ? #"Removed Columns" = Table.RemoveColumns(
? ? ? ? #"Added Custom",
? ? ? ? {"Count"}
? ? ),
? ? #"Added Custom1" = Table.AddColumn(
? ? ? ? #"Removed Columns",
? ? ? ? "range",
? ? ? ? each
? ? ? ? ? ? Table.AddIndexColumn (
? ? ? ? ? ? ? ? Table.PromoteHeaders (
? ? ? ? ? ? ? ? ? ? Table.SelectColumns (
? ? ? ? ? ? ? ? ? ? ? ? Table.Skip (range),
? ? ? ? ? ? ? ? ? ? ? ? [Columns]
? ? ? ? ? ? ? ? ? ? )
? ? ? ? ? ? ? ? ),
? ? ? ? ? ? ? ? "Index",1,1,Int64.Type
? ? ? ? ? ? )
? ? ),
? ? #"Remove Lists" = Table.RemoveColumns(
? ? ? ? #"Added Custom1",{"Columns"}
? ? )
in
? ? #"Remove Lists"        

? ??        

Reference above query as FirstBlock, filter on "Headers" value and expand range column:

Reference again Blocks query as Combined, unselect "Headers" in Country column and Add Custom column with FirstBlock table.

Now we need to join first block with each country table in "ranges" column. Use not very well known Table.Join() function which is missed in user interface:

Keep only Country and Data columns, expand the latest, remove Index column and unpivot all columns with Models

Now is only to pivot on types not to keep units and money in one column and apply proper data types

This query code

let
? ? Source = Blocks,
? ? #"Filtered Rows" = Table.SelectRows(
? ? ? ? Source,
? ? ? ? each ([Country] <> "Header")
? ? ),
? ? #"Added Custom" = Table.AddColumn(
? ? ? ? #"Filtered Rows",
? ? ? ? "FirstBlock",
? ? ? ? each FirstBlock
? ? ),
? ? #"Join with FirstBlock" = Table.AddColumn(
? ? ? ? #"Added Custom",
? ? ? ? "Data",
? ? ? ? each Table.Join([FirstBlock], "Index", [range], "Index")
? ? ),
? ? #"Removed Other Columns" = Table.SelectColumns(
? ? ? ? #"Join with FirstBlock",
? ? ? ? {"Country", "Data"}
? ? ),
? ? #"Expanded Data" = Table.ExpandTableColumn(
? ? ? ? #"Removed Other Columns",
? ? ? ? "Data",
? ? ? ? {"Month", "Type", "Index", "Model 1", "Model 2", "Model 3"}
? ? ),
? ? #"Remove Index" = Table.RemoveColumns(
? ? ? ? #"Expanded Data",
? ? ? ? {"Index"}
? ? ),
? ? #"Unpivot Models" = Table.UnpivotOtherColumns(
? ? ? ? #"Remove Index",
? ? ? ? {"Country", "Month", "Type"},
? ? ? ? "Model", "Value"
? ? ),
? ? #"Pivot Types" = Table.Pivot(
? ? ? ? #"Unpivot Models",
? ? ? ? List.Distinct(#"Unpivot Models"[Type]),
? ? ? ? "Type", "Value"
? ? ),
? ? #"Changed Type" = Table.TransformColumnTypes(
? ? ? ? #"Pivot Types",
? ? ? ? {
? ? ? ? ? ? {"Units", Int64.Type},
? ? ? ? ? ? {"Sales", Currency.Type},
? ? ? ? ? ? {"Country", type text},
? ? ? ? ? ? {"Model", type text},
? ? ? ? ? ? {"Month", type text}
? ? ? ? }
? ? )
in        

? 
? #"Changed Type"        

One more cosmetic thing is not to keep months as texts but transform to dates using end of each month dates for month indication.

Several ways to do that, we may use query as

let
? ? year = Date.Year(DateTime.LocalNow()),
? ? Source = List.Dates(#date(year, 1, 1), 12, #duration(32,0,0,0)),
? ? #"Converted to Table" = Table.FromList(
? ? ? ? Source,
? ? ? ? Splitter.SplitByNothing()
? ? ),
? ? #"Renamed Columns" = Table.RenameColumns(
? ? ? ? #"Converted to Table",
? ? ? ? {{"Column1", "Month"}}
? ? ),
? ? #"Calculated End of Month" = Table.TransformColumns(
? ? ? ? #"Renamed Columns",
? ? ? ? {{"Month", Date.EndOfMonth, type date}}
? ? ),
? ? #"Inserted Month Name" = Table.AddColumn(
? ? ? ? #"Calculated End of Month",
? ? ? ? "Month Name",
? ? ? ? each Date.MonthName([Month]),
? ? ? ? type text
? ? ),
? ? #"Extracted First Characters" = Table.TransformColumns(
? ? ? ? #"Inserted Month Name",
? ? ? ? {{"Month Name", each Text.Start(_, 3), type text}}
? ? )
in        

? 
? #"Extracted First Characters"        

Finally merge Combined query with MonthNames to extract dates and remove texts with month names

Now we are prepared for more deep analysis of our data. We may land above table to the grid, click on Analyze Data icon, ask to aggregate our data in such way or another

and immediately receive the result as this PivotTable or PivotChart.

Or use data model for more advance analysis, perhaps combining with other data we have.

Sample file is here