Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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
I need something like this... can you help
https://exceleratorbi.com.au/convert-a-cell-value-into-a-column-with-power-query/
@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
You are setting a fixed text value
"TITLE1"
what I need is to get the value on this cell, dynamic on each excel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.