Forum Discussion
How to invoke custom function to apply in files with different columns range?
I was following this tutorial to import files from a folder and clean them. However, for that example every .xls file has the same amount of columns. In my case, I have this (after cleaning):
Col1|Col2|
1|2|
2|2|
and
Col1|Col2|Col3|
1|2|2|
2|4|2|
And I want to have this result:
Col1|Col2|Col3|
1|2|-|
2|2|-|
1|2|2|
2|4|2|
My custom function looks like this (replaced columns by etc as they are almost two thousand)
(ExcelFile) =>
let
Source = Excel.Workbook(ExcelFile, null, true),
#"CMG Barras pesos_Sheet" = Source{[Item="CMG Barras pesos",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"CMG Barras pesos_Sheet",{"Column1", type any}, etc)
#"Removed Top Rows" = Table.Skip(#"Changed Type",8),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Barra", type any}, etc),
#"Removed Top Rows1" = Table.Skip(#"Changed Type1",1)
in
#"Removed Top Rows1"My suggestion would be to remove all Changed Type steps in your function since not all files have the same number of columns, therefore Power BI would throw an error if columns not found. Instead, perform Changed Type steps after you have already invoked your custom function.
1 Reply
My suggestion would be to remove all Changed Type steps in your function since not all files have the same number of columns, therefore Power BI would throw an error if columns not found. Instead, perform Changed Type steps after you have already invoked your custom function.