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!
Get rid of the #"Changed Type" step, but keep the rest of the step.
I recomment first UNPIVOTING all columns except the 1st column. That will give you a 3 column table:
- Column2, unchanged
- Attribute, which is the original column headers (a variable amount)
- Value (the number in each cell corresponding to the Column header)
Now you can change the data type of the Value column to a number.
Then select the column [Attribute], and click PIVOT column.
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!