Hi
I import a range from a workbook. In some files the range has 17 Columns some 19 and some only 8.
I do not know what keywords I should use to search for the Solution on the Forum so that the import is dynamic - any suggestions.
Thank You
Allister
BTW my current code is
let
Source = Excel.Workbook(File.Contents(pe_FilePath_LY_Workbook), null, true),
r_xPPE_DefinedName = Source{[Item=“r_xPPE”,Kind=“DefinedName”]}[Data],
#“Removed Other Columns” = Table.SelectColumns(r_xPPE_DefinedName,{“Column3”, “Column5”, “Column7”, “Column9”, “Column11”, “Column13”, “Column15”, “Column17”, “Column19”}),
#“Reordered Columns” = Table.ReorderColumns(#“Removed Other Columns”,{“Column3”, “Column19”, “Column5”, “Column7”, “Column9”, “Column11”, “Column13”, “Column15”, “Column17”}),
#“Removed Top Rows” = Table.Skip(#“Reordered Columns”,2),
#“Promoted Headers” = Table.PromoteHeaders(#“Removed Top Rows”, [PromoteAllScalars=true]),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Promoted Headers”, {“Column1”, “Column2”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Column1”, “Line Name Maj”}, {“Column2”, “Line Name Min”}, {“Attribute”, “FA Group”}}),
#“Filtered Rows2” = Table.SelectRows(#“Renamed Columns”, each Text.Contains([Line Name Min], “TY_”)),
#“Replaced Value” = Table.ReplaceValue(#“Filtered Rows2”,“TY”,“LY”,Replacer.ReplaceText,{“Line Name Min”}),
#“Duplicated Column” = Table.DuplicateColumn(#“Replaced Value”, “Line Name Min”, “Line Name Min - Copy”),
#“Added Conditional Column” = Table.AddColumn(#“Duplicated Column”, “Ledger”, each if Text.Contains([#“Line Name Min - Copy”], “ost”) then “Cost” else if Text.Contains([Line Name Min], “epr”) then “Depreciation” else if Text.Contains([Line Name Min], “arry”) then “Carrying Value” else 99),
#“Filtered Rows1” = Table.SelectRows(#“Added Conditional Column”, each ([Line Name Min] <> null) and ([Ledger] <> “Carrying Value”))
in
#“Filtered Rows1”
