Power Query - automatically change type of data inside the column (columns have changing titles)

Dear All, I am trying to develop file that will import data from another Excel file. The table that will be imported is similar to table from below. The problem that I have is how to make my q...

excel

SergeiBaklan

Jan 03, 2022

Carminol

To change #1 and #3 on text and rest on whole number

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    columnNames = Table.ColumnNames( Source ),
    changeType = Table.TransformColumnTypes(
        Source,
        List.Transform(
            columnNames,
            each
                if  List.PositionOf(columnNames, _) = 0 or
                    List.PositionOf(columnNames, _) = 2
                then {_, type text}
                else {_, Int64.Type}
        )
    )
in
    changeType

Names of columns and their number don't matter.

PQ change type.xlsx16 KB

PeterBartholomew1

Silver Contributor

Jan 03, 2022

Carminol

I will be interested to see the solutions offered by members who make regular use of PQ. My thought is that 'Unpivot Other Columns' will reduce the table to a standard database format without needing to reference the variable column names. The data can then be formatted as appropriate before repivoting.

Carminol

Copper Contributor

Jan 04, 2022

Unpivoting other columns and then repivoting worked in my case as well. Thank you.

Forum Discussion

Power Query - automatically change type of data inside the column (columns have changing titles)

Resources