Dynamic column changes handler

[Stuquan]

Hi all,

I have a specific problem around columns appearing/disppearing as my data refreshes daily. The source is from an API that pulls through all data for that day (I am unable to see what it will pull for that day until it has done so), somedays I might have 67 columns, the next 88 and vice versa. The error I get when I try to refresh in Power BI Service is that 'column 'X' does not exist in the rowset' and therefore it cannot refresh.

I am wondering if anyone knows a way of dynamically handling column changes e.g. being added/removed say in the advanced editor? I have tried to create a null table with known columns that are creating errors, but each day I get a new one so this is a very static approach and extremely manual. 

Many thanks.

[Thennarasu_R]

Try Selectedvalue Functions

Thanks,
Thennarasu

[DataInsights]

@Stuquan,

You can use the following M expression to refer to columns by position (0 is the first column):

Table.ColumnNames(PreviousStep){0}

One approach would be to get a column count and loop through each column, incrementing the number in curly braces. Example: iteration 1: {0}, iteration 2: {1}, until you reach the end.

[Stuquan]

Hi @DataInsights, thanks for this insight! The main issue with this is that I do not know all the columns that exist currently, the API connector I'm using seems to be very limited in that it will only return data (daily) for that day, the next day will then have more or less different columns and when that change happens it then fails.

[DataInsights · Accepted solution]

@Stuquan,

Right, that's why I was thinking of dynamically getting a column count each day. You would essentially be creating dynamic M that adjusts to the number of columns available on any given day.

I did some research and found the links below. The first link seems like the simplest approach, which is to merge all columns using a delimiter, and then split the column by that delimiter.

https://www.youtube.com/watch?v=dYXVTAIOcw0 

https://community.powerbi.com/t5/Power-Query/Split-varying-number-of-columns-into-4-columns-each/m-p... 

The example below uses the parameter MissingField.Ignore. I guess you could list the maximum number of columns you might have, and then this parameter would cause any non-existent columns to be ignored.

https://www.mrexcel.com/board/threads/powerquery-pdf-file-with-varying-number-of-columns.1218981/