CODEWITHSUNDEEP
powerbidaxpowerquerym
SRel
SRel

Reputation: 423

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

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?

Upvotes: 0

Views: 1797

Answers (2)

YuryPetrachenko
YuryPetrachenko

Reputation: 1

You can use try otherwise:

    #"Change Type ID" = try Table.TransformColumnTypes(#"Höher gestufte Header",{{"ID", Int64.Type}}) otherwise #"Höher gestufte Header",
    #"Change Type Country" = try Table.TransformColumnTypes(#"Change Type ID",{{"Country", type text}}) otherwise #"Change Type ID",
    ...

Upvotes: 0

chillin
chillin

Reputation: 4486

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

Upvotes: 4

Related Questions