Power-Query/Power-Bi: How to change column types only if they exists?

0
votes

I load a huge excel table into power-query (using import .csv). This import automatically detects the column-types and change those respectively:

Table.TransformColumnTypes(#"Höher gestufte Header",{{"ID", Int64.Type}, {"Country", type text}, {"Customer", type text}, {"Release Name", type text}, {"Hardware Systems", type text}, {"Service By", type text}, {"Hwirelease Tags", type text}, {"Country Tags", type text}, {"Created", type datetime}, {"Last Change", type datetime}, {"Scope", type text}, {"PPM PID", Int64.Type}, {"Salesforce IDs", type text}})

Problem:

The problem begins when I know change the source to a different .csv from an older date in which some of those columns mentioned before did nox exists (e.g. PPM PID). I receive an error and have to manually delete the command which tries to change the respective column.

Question:

Is there some easy workaround, so that Power-Query only tried to change the columntype if the column exists? Or is my complete approach bad when column names tend to change it names over time?

1
powerbidaxpowerquerym

1 Answers

3
votes

One way to do this might be to:

  1. Create a structure of pairs (i.e. list or record), where each pair represents a column name and its corresponding type. Include all possible columns (those which exist and those which don't).
  2. Filter the collection to only keep the columns which are present in your table and then pass the filtered collection to Table.TransformColumnTypes.

To give an example:

let
    #"Höher gestufte Header" = Table.FromColumns({
        {1, 6, 45, 67},
        {"US", "JA", "CA", "GB"}
    }, {"ID", "Country"}),
    typeTransformations = {{"ID", Int64.Type}, {"Country", type text}, {"Customer", type text}, {"Release Name", type text}, {"Hardware Systems", type text}, {"Service By", type text}, {"Hwirelease Tags", type text}, {"Country Tags", type text}, {"Created", type datetime}, {"Last Change", type datetime}, {"Scope", type text}, {"PPM PID", Int64.Type}, {"Salesforce IDs", type text}},
    changeTypes = Table.TransformColumnTypes(#"Höher gestufte Header", List.Select(typeTransformations, each Table.HasColumns(#"Höher gestufte Header", _{0})))
in
    changeTypes