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 query to atomatically sign column type to every column as columns titles in the source file will change every day I import it .

There are several conditions that need to be met:

 - Columns 1 - 3 have always the same titles and the same type of data (column 1 and 3 - text, column 2 - whole number).

- Starting from column 4 till the end of the table data type is whole number.

- The number of columns in the table may change (decrease or encrease).

- Starting from column 4 titles of columns will change every day (column 4 is always today's date, column 5 the next day and so on).

Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number
TextWhole numberTextWhole numberWhole numberWhole numberWhole numberWhole number

Thank you in advance good people! 🙂

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.

9 Replies

  • 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

      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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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.

  • PeterBartholomew1's avatar
    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's avatar
      Carminol
      Copper Contributor
      Jan 04, 2022
      Unpivoting other columns and then repivoting worked in my case as well. Thank you.

Resources