Forum Discussion

alex's avatar
alex
Helper I
7 years ago
Solved

Move Sheet Titles to Rows ( Power Query )

I commonly run into source data XLS formated liked so: Sheet Title1;                          ;Col.Title1        ; Col.Title 2   ; Col.Title 3   ; Col.Title 4  RowTitle1          ; Value          ...
Need Help
  • DaFloDo's avatar
    DaFloDo
    7 years ago

    hi alex,

     

    this is how I achieved the result ...

     

     

    you'll have to make some adjustments to fit your needs, but in general it should be possible ... here is the code to get you started:

    let
    Quelle = Excel.Workbook(File.Contents("pathtoexcelfile.xlsx"), null, true),
    Tabelle1 = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    SheetTitle = List.First(Tabelle1[Column1]),
    tbl2 = Table.RemoveRows(Tabelle1, 0),
    tbl3 = Table.PromoteHeaders(tbl2, [PromoteAllScalars=true]),
    tbl4 = Table.RenameColumns(tbl3,{{"Column1", "RowTitle"}}),
    tbl5 = Table.AddColumn(tbl4, "SheetTitle", each SheetTitle, type text)
in
    tbl5

     

     

    best regards

     

    florian

DaFloDo's avatar
DaFloDo
Resolver I
7 years ago

hi alex,

 

this is how I achieved the result ...

 

 

you'll have to make some adjustments to fit your needs, but in general it should be possible ... here is the code to get you started:

let
    Quelle = Excel.Workbook(File.Contents("pathtoexcelfile.xlsx"), null, true),
    Tabelle1 = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    SheetTitle = List.First(Tabelle1[Column1]),
    tbl2 = Table.RemoveRows(Tabelle1, 0),
    tbl3 = Table.PromoteHeaders(tbl2, [PromoteAllScalars=true]),
    tbl4 = Table.RenameColumns(tbl3,{{"Column1", "RowTitle"}}),
    tbl5 = Table.AddColumn(tbl4, "SheetTitle", each SheetTitle, type text)
in
    tbl5

 

 

best regards

 

florian