I have made a custom function to transform files in a power query:
let
Source = (#"Sample File Parameter1" as binary) => let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),
#"Sjælland m# kl# 1" = Source{[Name="Sjælland m# kl# 0"]}[Data],
#"Removed Top Rows" = Table.Skip(#"Sjælland m# kl# 1",6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"LAB Nr", type text}, {"Column3", type text}, {"PrøveId", type text}, {"Dybde", type text}, {"Column6", type text}, {"Enhed", type text}, {"Klassificering", type text}, {"Bly (Pb)", type text}, {"Column10", type text}, {"Cadmium (Cd)", type text}, {"Chrom Total (Cr total)", type text}, {"Kobber (Cu)", type number}, {"Nikkel (Ni)", Int64.Type}, {"Zink (Zn)", Int64.Type}, {"Benzen", type text}, {"BTEX total", type text}, {"Benz(a)pyren", type text}, {"Dibenz(a,h)antracen", type text}, {"PAH total", type number}, {"Flygtige (Benzin) (C6-C10)", type text}, {"Let olie (C10-C15)", type text}, {"Let olie (C15-C20)", type text}, {"Tung olie (C20-C35)", type text}, {"Olie Total (C6-C35)", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"PrøveId", "Dybde", "Bly (Pb)", "Cadmium (Cd)", "Chrom Total (Cr total)", "Kobber (Cu)", "Nikkel (Ni)", "Zink (Zn)", "Benzen", "BTEX total", "Benz(a)pyren", "Dibenz(a,h)antracen", "PAH total", "Flygtige (Benzin) (C6-C10)", "Let olie (C10-C15)", "Let olie (C15-C20)", "Tung olie (C20-C35)", "Olie Total (C6-C35)"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"Olie Total (C6-C35)"] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Top", each Number.ToText(Number.FromText(Text.Start([Dybde],Text.PositionOf([Dybde],"-"))),"0.0")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Key", each [PrøveId]&"-"&[Top]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Bly (Pb)", "Cadmium (Cd)", "Chrom Total (Cr total)", "Kobber (Cu)", "Nikkel (Ni)", "Zink (Zn)", "Benzen", "BTEX total", "Benz(a)pyren", "Dibenz(a,h)antracen", "PAH total", "Flygtige (Benzin) (C6-C10)", "Let olie (C10-C15)", "Let olie (C15-C20)", "Tung olie (C20-C35)", "Olie Total (C6-C35)", "Key"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Other Columns1", {"Bly (Pb)", "Cadmium (Cd)", "Chrom Total (Cr total)", "Kobber (Cu)", "Nikkel (Ni)", "Zink (Zn)", "Benzen", "BTEX total", "Benz(a)pyren", "Dibenz(a,h)antracen", "PAH total", "Flygtige (Benzin) (C6-C10)", "Let olie (C10-C15)", "Let olie (C15-C20)", "Tung olie (C20-C35)", "Olie Total (C6-C35)"}, "Attribute", "Value" )
in
#"Unpivoted Only Selected Columns"
in
Source
I then run the following query to do this to all files in the folder:
let
Source = Folder.Files(ResultsTable),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Analyseresultater", each #"Transform File from Analyseresultater"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Analyseresultater"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Analyseresultater", Table.ColumnNames(#"Transform File from Analyseresultater"(#"Sample File"))),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"Key", "Attribute", "Value"})
in
#"Removed Other Columns"
My problem then is that the colums "Benzen" and "BTEX Total" are not always present, so I get an error:
Expression.Error: The column 'Benzen' of the table wasn't found. Details: Benzen
I have tried using MissingField.Ignore og MissingField.UseNull without luck, so now I turn to StackOverflow for help, can someone help me get past this error :) Hope it's possible
