Read a complex excel file with Power Query

[dpombal]

Hi all,

I have some excel files with this format (Problem Is I need to gather Title 1 and title 2 cell values)

I need to keep all Green values, Table below and Title1 and Title 2 values

I need to add this 2 values Title 1 and Title 2 as 2 new columns

 Something like this should be my final table

Here it is attached Excel source (https://1drv.ms/x/s!Am7buNMZi-gwn3FB9LodiYT-VQTk)

Regards

[v-yuezhe-msft]

@dpombal,

Add a blank query in Power BI Desktop, paste one of the following code to the Advanced Editor of the blank query .

let
    Source = Excel.Workbook(File.Contents("yourfolderpath\My_File.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Bottom Rows" = Table.RemoveLastN(Sheet1_Sheet,4),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Column1] <> null and [Column1] <> "")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each "TITLE1"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each "Title2"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22"}),
    #"Removed Top Rows" = Table.Skip(#"Reordered Columns",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TITLE1", type text}, {"Title2", type text}, {"Jersey", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"Min", type number}, {"T Tot ", Int64.Type}, {"T Tot  / min", Int64.Type}, {"T  1ª P", Int64.Type}, {"T  1ª P / min", Int64.Type}, {"T  2ª P", Int64.Type}, {"T  2ª P / min", Int64.Type}, {"T Tot  CP", Int64.Type}, {"T Tot OP", Int64.Type}, {"T Tot  TNE", Int64.Type}, {"T  1ªP CP", Int64.Type}, {"T  1ªP OPP", Int64.Type}, {"T  1ªP TNE", Int64.Type}, {"T  2ªP CPP", Int64.Type}, {"T  2ªP OEP", Int64.Type}, {"T  2ªP TNE", Int64.Type}, {"T Tot  1-2", Int64.Type}, {"T  1ª 1-2", Int64.Type}, {"T  2ª  1-2", Int64.Type}})
in
    #"Changed Type"

let
    Source = Excel.Workbook(File.Contents("yourfolderpath\My_File.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Bottom Rows" = Table.RemoveLastN(Sheet1_Sheet,4),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Column1] <> null and [Column1] <> "")),
    #"Duplicated Column" = Table.AddColumn(#"Filtered Rows", "Column1 - Copy", each [Column1], type any),
    #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each if [#"Column1 - Copy"]="TITLE1" then [#"Column1 - Copy"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TITLE1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"TITLE1_1", type any}, {"TITLE1_2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if [TITLE1_1]="Title2" then [TITLE1_1] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down1",{"TITLE1_2", "Custom", "TITLE1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "TITLE1_1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"TITLE1_1"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"TITLE1", type text}, {"Title2", type text}, {"Jersey", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"Min", type number}, {"T Tot ", Int64.Type}, {"T Tot  / min", Int64.Type}, {"T  1ª P", Int64.Type}, {"T  1ª P / min", Int64.Type}, {"T  2ª P", Int64.Type}, {"T  2ª P / min", Int64.Type}, {"T Tot  CP", Int64.Type}, {"T Tot OP", Int64.Type}, {"T Tot  TNE", Int64.Type}, {"T  1ªP CP", Int64.Type}, {"T  1ªP OPP", Int64.Type}, {"T  1ªP TNE", Int64.Type}, {"T  2ªP CPP", Int64.Type}, {"T  2ªP OEP", Int64.Type}, {"T  2ªP TNE", Int64.Type}, {"T Tot  1-2", Int64.Type}, {"T  1ª 1-2", Int64.Type}, {"T  2ª  1-2", Int64.Type}})
in
    #"Changed Type1"

Regards,
Lydia

[dpombal]

I need something like this...  can you help

https://exceleratorbi.com.au/convert-a-cell-value-into-a-column-with-power-query/

[v-yuezhe-msft · Accepted solution]

@dpombal,

Please use the following M code instead.

let
    Source = Excel.Workbook(File.Contents("yourfolderpath\My_File.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Bottom Rows" = Table.RemoveLastN(Sheet1_Sheet,4),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Column1] <> null and [Column1] <> "")),
    Custom1 = Record.Field(#"Filtered Rows"{0},"Column1"),
    Custom3 = Record.Field(#"Filtered Rows"{1},"Column1"),
    Custom2 = #"Filtered Rows",
    #"Added Custom" = Table.AddColumn(Custom2, "Custom", each Custom1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Custom3),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22"}),
    #"Removed Top Rows" = Table.Skip(#"Reordered Columns",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TITLE1", type text}, {"Title2", type text}, {"Jersey", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"Min", type number}, {"T Tot ", Int64.Type}, {"T Tot  / min", Int64.Type}, {"T  1ª P", Int64.Type}, {"T  1ª P / min", Int64.Type}, {"T  2ª P", Int64.Type}, {"T  2ª P / min", Int64.Type}, {"T Tot  CP", Int64.Type}, {"T Tot OP", Int64.Type}, {"T Tot  TNE", Int64.Type}, {"T  1ªP CP", Int64.Type}, {"T  1ªP OPP", Int64.Type}, {"T  1ªP TNE", Int64.Type}, {"T  2ªP CPP", Int64.Type}, {"T  2ªP OEP", Int64.Type}, {"T  2ªP TNE", Int64.Type}, {"T Tot  1-2", Int64.Type}, {"T  1ª 1-2", Int64.Type}, {"T  2ª  1-2", Int64.Type}})
in
    #"Changed Type"

Regards,
Lydia

[dpombal]

You are setting a fixed text value 

"TITLE1"

what I need is to get the value on this cell, dynamic on each excel