Power query - dynamic column type

[Anonymous]

Hi everyone

I'm got a really simple query but I'm struggling to change the type of specific columns. I built most of it through the UI. I basically want to change all columns to type number except for the first column. The complication is the source url I'm pulling the data from has a number of columns that will change quite regularly. So basically want to code column 2-X as a number (where x equals total columns - 1). 

Thanks in advance!

let
    Source = Web.Page(Web.Contents("https://www.oddschecker.com/golf/singapore-open/winner")),
    Data = Source{1}[Data],
    #"Demoted Headers" = Table.DemoteHeaders(Data),
    #"Removed Top Rows" = Table.Skip(#"Demoted Headers",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type number}, {"Column14", type number}, {"Column15", type number}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type number}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}})
in
    #"Changed Type"

[Anonymous]

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:

1. Column2, unchanged
2. Attribute, which is the original column headers (a variable amount)
3. 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.

[Anonymous]

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

[Anonymous · Accepted solution]

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!