Is it possible to use the column header in a concatenation function?

[Anonymous]

I have a list of currencies:

• CAD
• USD
• EUR

I would like to combine these with a list of particular currencies in every possible combination:

The only method I could think of was creating a table that had rows with the currencies and column headers with the currencies as well (as shown in my Excel image).

However, I can't seem to pull the name of the header using any function I know to use in concatenating when I try to do the same thing in Power BI. Is this possible with DAX, or is there a tool that will do this programmatically so that the values will update when I add in a new row?

[v-jiascu-msft]

Hi @Anonymous,

What's the actually expected result? Just see from the image, you could try this formula as a calculated column.

CAD = [Currency] & "CAD"

EUR = [Currency] & "ERU"

Can you share a sample?

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft, the expected result would be something akin to how you can use the header for the "current column" in an Excel table and concatenate that with whatever is in the list on the left-hand side. I have demoed this in the Table sheet of the Currency Generator file here. The result I would like in PBI is sort of demonstrated in the Concat. Currencies sheet where I use the contents from the Source List sheet, create the headers using the Functional Currencies, and then concatenate using said values.

I have also included a PBIX file that contains the values split up into separate tables, but I haven't done much beyond that as no solution has allowed me to join the two tables' contents in the way I would like.

Ideally, I would like to create a more "elegant" DAX solution that would reference the contents in the two tables and combine them in a third but do so programmatically with every possible combination of currencies from the two lists without me having to create a new column, name it, and paste in the concatenation formula. If I end up with a hundred currencies, there will be a fair amount of manual up-front work I'd like to avoid.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I guess you have other columns in the real data. But I think the demo in the attachments could give you some ideas. I'm afraid it's hard to do it with DAX. How about the Power Query?

1. Add a custom column. Actually we add another table.

#"Functional Currencies"

2. Expand the custom column.

3. Add another custom column.

[Currency] & [Custom.Currency]

4. Select the two custom columns with "CTRL". Pivot these columns. 

Now the values will update automatically when new data add in. 

Best Regards,

Dale

[Anonymous]

This is perfect, @v-jiascu-msft. Thank you!