Forum Discussion
Parse data before import to dataset
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
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 CustomRegards,
Xiaoxin Sheng
2 Replies
- lbendlin
Super User
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.
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 CustomRegards,
Xiaoxin Sheng
