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 04, 2022

Carminol

I played with this a bit before lunch. You have to know the name of the Table, but you can get away with not knowing the column names (as SergeiBaklan suggests).

"Dates"
 = INDEX(Table1[#Headers], 3+day)
"Values"
 = INDEX(Table1, 0, 3+day)

With 365 that can be dressed up a bit with Lambda functions, to give

"Date"
 = Dateλ(day)
"Values"
 = Valuesλ(Dateλ(2))

where 'Valuesλ' refers to

= LAMBDA(dd,
    XLOOKUP(dd, Table1[#Headers], Table1)
  )

SergeiBaklan

Diamond Contributor

Jan 04, 2022

PeterBartholomew1 , hope this exercise didn't affect you appetite.

Carminol , it's better to have sample file to be more concrete in our suggestions.

Forum Discussion

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

Resources