Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create a new custom column by formula, and I want my formula to refer to columns by their index instead of thier name.
As a simple example, I tried the below attempting to create a column called "1" that would be equal to the column with index 3
= Table.AddColumn(#"Changed Type", "1", each Table.ColumnNames(#"Changed Type"){3})
This instead created a column where every entry was equal to the column header of the column with index 3. I also tried
= Table.AddColumn(#"Changed Type", "1", each [Table.ColumnNames(#"Changed Type"){3}])
But this returned an error. Any assistance would be much appreciated.
Edit:
Here is an example to show what I am trying to do. This is a simplified version of the forcast data that I get each month. I want to add columns that automatically calculate cumulative values from the first month in the table (June-21, in this case) to each other month in the table. The formula that I use to do this cannot be dependant on column headers as these will change each month. (So a new column for just June-21, a column for June-21 plus July-21, a column for June-21 plus July-21 plus August-21)
Material | June-21 | July-21 | August-21 |
C50870045-5L01 | 300 | 200 | 0 |
C50SX393B-2L01 | 0 | 100 | 400 |
Best regards
Sam Bolsover
I believe I figured out how to make it work:
#"Step Name" = Table.AddColumn (
#"Previous Step Name", "New Column Name",
each Record.Field(_, Table.ColumnNames(#"Previous Step Name"){3}))
Hi @Sam_Bolsover ,
It sounds like you need an alias column, which is pretty easy to build in Power Query using a conditional column. However if you could show us a mockup maybe in Excel for a sample input and sample output that would be good.
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
I too am having a very similar issue. I am trying to add 2 columns by index/position number together in a custom column. I've looked all over the place and nobody seems to have an answer. Here's hoping someone can help!
Thanks Nathaniel, I've added some dummy data in my post to help explain what I am trying to do.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |