Jan 03 2022 03:50 AM
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! :)
Jan 03 2022 04:11 AM
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.
Jan 03 2022 05:15 AM
SolutionTo 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.
Jan 04 2022 02:12 AM
Jan 04 2022 02:16 AM
Jan 04 2022 02:44 AM
Jan 04 2022 04:13 AM
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.
Jan 04 2022 05:51 AM - edited Jan 04 2022 05:58 AM
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)
)
Jan 04 2022 05:58 AM
@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.
Jan 03 2022 05:15 AM
SolutionTo 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.