Forum Discussion

Carminol's avatar
Carminol
Copper Contributor
Jan 03, 2022
Solved

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's avatar
    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.

Carminol's avatar
Carminol
Copper Contributor
Jan 04, 2022
Guys, I decided to ask you to help one more time.
I also use the imported table shown in description of this discussion in futher calculations (there are fomulas in different sheets that take data from this table) . But of course every day, when I update main table with PQ (every day source file changes) - all the formulas in my file break down (have "#ARG" mistake). Is there a way to avoid this kind of mistake so that formulas in other sheets work properly after I update main table?
Thank you so much for helping me.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 04, 2022

Carminol 

If  I understood correctly table returned by Power Query has different headers after each daily update, formulae in sheet do not recognize these new headers.

 

If so, in  any case you shall identify somehow columns in main table. That could be by name or by position.

If by name you may use pre-defined column names and change on them in Power Query ignoring column names in source file.

If by position most probably you shall re-write formulae to work with column positions.

Resources