Forum Discussion
Power query - dynamic column type
I recommend reading up on Gil Raviv's 10 blogs on pitfalls of Query Editor.
I think this blog in particular will be what you need to think about.
To summarize, Table.ColumnNames(Table) will generate a list of column names. Wrap that with a List.Count(). Now you know how many columns you have. If that is stored a step, you have access to a variable with how many columns there are. Now you can refer to columns by their 0-based index off of that variable. I believe you can do a Replace Values step with that column index.
Happy hunting!
Thanks Chris, didn't think of it that way!
It does work. However, I'm still hoping to keep the original columns,even if they only contain null values, this method gets rid of the blank columns. Could of course convert the nulls to something else but it creates the same problem in that I don't know how to ensure it doens't break down when the number of columns change
I recommend reading up on Gil Raviv's 10 blogs on pitfalls of Query Editor.
I think this blog in particular will be what you need to think about.
To summarize, Table.ColumnNames(Table) will generate a list of column names. Wrap that with a List.Count(). Now you know how many columns you have. If that is stored a step, you have access to a variable with how many columns there are. Now you can refer to columns by their 0-based index off of that variable. I believe you can do a Replace Values step with that column index.
Happy hunting!