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

[Carminol]

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 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number
Text	Whole number	Text	Whole number	Whole number	Whole number	Whole number	Whole number

Thank you in advance good people! :)

[#2]

@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.

[#3 · Accepted solution]

@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.

[#4]

Thank you, it works perfectly :)

[#5]

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

[#6]

@Carminol , you are welcome

[#7]

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.

[#8]

@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.

[#9]

@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 @Sergei Baklan 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)
  )

[#10]

@Peter Bartholomew , hope this exercise didn't affect you appetite.

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