Creating a Calculated table with the values of another table

[Anonymous]

Hello All,

I'm looking to create a new table in my power BI data model whereby the values in a column from the first table become the columns of the other table (like a pivot table), however, I want to use the second table as a table in the model not the matrix visual in the report. what I want to accomplish is effectively below. I want to create measures based on Table 2.

Table 1

Ticket	Task	Complete Date
Ticket1	Task1	May1
Ticket1	Task2	May2
Ticket1	Task3	May3
Ticket2	Task1	may2

Table 2

Ticket	Task1	Task2	Task3
Ticket1	May1	May2	May3
Ticket2	May2

[amitchandak]

@Anonymous , not very clear, see if concatenatex can help

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

[Anonymous · Accepted solution]

Hello,

Thank you for the reply, I actually found my solution here:
https://community.powerbi.com/t5/Desktop/Calculated-table-with-columns-based-on-the-values-from-anot...

Pivoting one of the columns with a max date as the aggregate value is what I was looking for.

[BA_Pete]

Hi @Anonymous ,

Well done on finding the solution.

You should mark your own answer as the solution to this. This will ensure that others are directed straight to the post with the answer in it, not to a reply that contains no useful information.

Pete

[v-lili6-msft]

hi @Anonymous 

It's pleasant that your problem has been solved, 😁 please accept my reply as answer, that way, other community members would easily find the solution when they get same issues.  

Regards,

Lin

[kentyler]

In Power Query you can create a "reference" copy of your Table 1, and then unpovit the reference copy...which will give you a table like table 2 that will appear in your data model and that you can use just like any other table.... it will be updated everytime you refresh your data sources.