Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sam_Bolsover
Frequent Visitor

Referencing columns in formulas by index?

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)

 

MaterialJune-21July-21August-21
C50870045-5L013002000
C50SX393B-2L010100400

 

 

Best regards

Sam Bolsover

4 REPLIES 4
Gabe_V
Helper I
Helper I

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}))

Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.