Transform more than 100 Ranges in same worksheet in Tables with Power Query or Power BI

[PedroB1969]

1. One file, one worksheet
<https://ec.europa.eu/info/sites/default/files/economy-finance/cross_country_tables_ar2021_0.xlsx>
2. More than 100 potencial Tables
3. The Same Header in each diferrent Range
4. The Name of table related to Description

How I can transform this for work in Power BI?

Thanks in advance
Pedro

 

[p45cal]

In the linked-to file there is:
The original layout of the data in the sheet cross-country tables_only_final, it's ALL the data made into a single table.
On sheet Table2 a pivot table based on a Power Query query of the cross-country tables_only_final sheet. The Power Query re-arranges the data so it's like the little sample of data on the Arrangement sheet, which makes it suitable for a pivot table of your design. I've just modelled the pivot table so it mimics the arrangement of the original data.
The data hasn't been added to the Data Model but it's easy to do if you want. The data is in a state to be used in Power BI.

3. The Same Header in each diferrent Range

Not quite, column C sometimes has Ch 19-70, other times Avg 19-70, sometimes no header and no data. These have been given their own column (headed Ch,Avg,n/a) and converted to Ch, Avg and n/a respectively, so you can see that the figures represent change, average over the years 2019-2070. The values for these are in rows where the Years value is '19-70'. There's no real need for those rows of figures because they're probably all calculated from the other columns so a pivot table could do that. I would filter '19-70' out of any pivot; maybe I shouldn't have bothered with it.

Link to file: https://app.box.com/s/yuhd9plchspx0e61u29dq1sazqcx4w1o

 

[PedroB1969]

Perfect. The solution is clear, and it works. Everything is ready to be transformed into a table by selecting the Table Name. How are the tables generated individually? Great Team

 

[PedroB1969]

The file

 

[p45cal]

Looks good, so added the same pivot, made sure the pivot option to save source data with the file was turned off to keep the file small and used Anonymous credentials to connect.

How are the tables generated individually?

With the pivot arrangement - your choice.

 

[PedroB1969]

Of course. Thanks very much. Perfect Solution

 

[Excel Wizard]

Please try

let
    Source = Excel.Workbook(Web.Contents("https://ec.europa.eu/info/sites/default/files/economy-finance/cross_country_tables_ar2021_0.xlsx"), null, true),
    #"cross-country tables_only_final_Sheet" = Source{[Item="cross-country tables_only_final",Kind="Sheet"]}[Data],
    Grouped = Table.Group(#"cross-country tables_only_final_Sheet", "Column2", 
        {{"A", each Table.RemoveColumns(Table.AddColumn(Table.PromoteHeaders(Table.RemoveFirstN(_,2)),"Table", (x)=> [Column2]{0}),"Column1")  }}
        ,0,(b,e)=>Number.From(Text.Start(e,5)="Table")),
    Combine = Table.Combine(List.Skip(Grouped[A],1)),
    RemovedErrors = Table.RemoveRowsWithErrors(Combine, {"Ch 19-70"}),
    Unpivoted = Table.UnpivotOtherColumns(RemovedErrors, {"Table", "Country"}, "Year", "Value")
in
    Unpivoted