Parse data before import to dataset

[mdobrescu]

Hello,

I have a CSV file hosted on OneDrive Bussines which is linked to a PowerBi dataset. 

Lets say both the CSV file and Powerbi dataset have 3 columns(column1, column2, column3).

But sometimes the CSV file after an export has only 2 columns(column1,column3). Because the column2 is missing from the CSV, on PowerBi dataset refresh, I'll get an error saying that column2 is missing. 

So the questions is, if it is possible to parse the values before the imports similar to this:

if (CSV.column2 is null) ? {Dataset.column2.value = null) : {Dataset.column2.value = CSV.column2.value)

Thank you,

Mihai

[Anonymous · Accepted solution]

HI @mdobrescu,

According to your description, I'd like to suggest you can add a custom query step with an if statement to check the data source fields. Then use the current result or try to use add column function to add these missed columns.

Table.ColumnCount - PowerQuery M | Microsoft Docs

Table.AddColumn - PowerQuery M | Microsoft Docs

    Source = xxxxxx,
    Custom=
        if Table.ColumnCount(Source) > 2 then
            Source
        else
            Table.AddColumn(
                Source,
                "column2",
                each null
            )
in
    Custom

Regards,

Xiaoxin Sheng

[lbendlin]

yes, that is possible.  Change the query to remove the reference to the expected number of columns, and then use try ... otyherwise ...  in Power Query to handle your scenarios.