question

MarcBergmans-8619 avatar image
0 Votes"
MarcBergmans-8619 asked ·

Is it possible to calculate in Excel Power Query without using the names of the Columns ?

Hello,

Hello,

I need to make Excel Power Query in one column the sum of two columns and in another column i need to subtract them.
I cannot use the names of the columns because they change with every refresh.
So i want to know if it is possible to make calculations in Power Excel without using the names of the column.
Something like in Excel Column C + Column D and Column C minus Column D
So that the formula's stays correct if the Column names changes.
Is this possible ? If yes how can i do this ?

Many Thanks,

Marc.

power-query-desktoppower-query-m
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @MarcBergmans-8619

Answer is yes or no. You need to know their position in the source table or there must be a consistent pattern to find them out. Below is one way when you know their postion in Table1 below

72980-demo.png

 let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
     // Assuming all columns - but [Product] - are of type number
     ChangedTypes = Table.TransformColumnTypes(Source,
         {
                 {"Product", type text}} &
         List.Transform(
             List.RemoveItems(Table.ColumnNames(Source),{"Product"}), each
             {_, type number}
         )
     ),
     // Want to SUM the 1st and and 3rd columns with numbers
     // ([Product] is the 1st column at Index 0)
     DesiredField = Table.SelectRows(Table.Schema(ChangedTypes), each
         List.Contains({1,3}, [Position])
     )[Name],
     mySum = Table.AddColumn(ChangedTypes, "SUM", each
         List.Sum(
             Record.ToList(
                 Record.SelectFields(_, DesiredField)
             )
         ), type number
     )
 in
     mySum


A series of article that relate to dynamic colums



demo.png (15.0 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MattAllington-5456 avatar image
0 Votes"
MattAllington-5456 answered ·

My suggestion is to demote the headings. This is opposite of “use first row as header”. Instead you can “use header as first row”. Then remove the first row. You will then have standard column names based on position.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.