Formatting Data in Power Query Editor when it is wrapped in formatting

Hi , petermb72 

According to your description, you want to use Power Query to format your data .

For your need , We need to make sure your file is in the same format every time. And the format like this in my understanding:

If this , we can connect the excel and we can create a blank query and put this M code in Power Query Editor:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yueyzhang\Downloads\case\20230714.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Added Index" = Table.AddIndexColumn(Tabelle1_Sheet, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (x)=>if x[Column1]=null then try if Table.SelectRows(#"Added Index",(y)=> y[Index]=x[Index]-1 ){0}[Column1]=null then "Date" else Table.SelectRows(#"Added Index",(y)=> y[Index]=x[Index]-1 ){0}[Column1] otherwise "Date"  else x[Column1]            ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Column1", "Column2", "Index", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom.1", each if [Index] >= 13 then 5 else if [Index] >= 10 then 4 else if [Index] >= 7 then 3 else if [Index] >= 4 then 2 else 1,  Int64.Type),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column2", "Custom.1", "Index", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    Custom2 = Table.Group(#"Changed Type",{"Custom","Custom.1"},{"test",(x)=>Table.RemoveColumns(x,{ "Custom", "Custom.1", "Index"})}),
    #"Added Index1" = Table.AddIndexColumn(Custom2, "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index1", "Custom.2", (x)=>
if x[Custom]="Date" then null else Table.PromoteHeaders(Table.Sort(Table.SelectRows(#"Added Index1",(y)=>y[Index]<x[Index] and y[Custom]="Date" ),{"Index",1}){0}[test], [PromoteAllScalars=true])
  ),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.3", each List.Zip( { Table.ColumnNames([test]), Table.ColumnNames([Custom.2]) } )        ),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.4", each 
Table.SelectRows(Table.UnpivotOtherColumns(Table.RenameColumns([test],[Custom.3]), {"Column1"}, "Attribute", "Value")
, each ([Value] <> "null"))
),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom3", {"Custom.4"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Errors",{"Custom.1", "test", "Index", "Custom.2", "Custom.3"}),
    #"Expanded Custom.4" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.4", {"Column1", "Attribute", "Value"}, {"Column1", "Attribute", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.4",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}, {"Custom", "Unit"}, {"Column1", "Type"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type2"

You can update the file location in your side .

Then we can get this table:

And we can put the fields on the visual and we can get this:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank You so much for your help!