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

  • 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