Changing Data Format

[Anonymous]

Hi guys,

I have a table like the one on the left. I want to transform this table into the one on the right. Is it possible with Power Query or SQL? If so, how can I do it?

[Uzi2019 · Accepted solution]

Hi @Anonymous 

Do you want to have a seperate table??? 

Like this??

then take new table from modeling tab then write this 

Summarize table = SUMMARIZE(Table3,Table3[RN],
"Min CNV",CALCULATE(MIN(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])),
"Max CNV",CALCULATE(Max(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])))

I hope I answered your question!

[Anonymous · Accepted solution]

Hi @Anonymous ,

If the first and last are determined by the [Score] field, try creating a calculated table with the following code:

Table2 = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    "first_CVN",
    VAR __min_score = CALCULATE( MIN('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__min_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result,
    "last_CVN",
    VAR __max_score = CALCULATE( MAX('Table'[Score]), ALLEXCEPT('Table','Table'[ID]) )
    VAR __result = CALCULATE(MAX('Table'[Cvn]),'Table'[Score]=__max_score,ALLEXCEPT('Table','Table'[ID]))
    RETURN
    __result
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Uzi2019]

hi @Anonymous 

just simply take matrix visual and add two times cvn and set to first and last

I hope I naswered your question!

[Anonymous]

I was planning to create a relationship table with a matrix table, but I couldn't do it with matrix table

[Uzi2019]

Hi @Anonymous 

I didnt understand you query..
Relationship table with Matrix table???

[Anonymous]

That's my bad, sorry. The left table is already inserted into my report. I want to create two new columns called first_cvn and last_cvn. The first_cvn value should come from the minimum score, and the last_cvn value should come from the highest score. I am going to use this table as a join table; due to this reason, I couldn't use image solutions. 😞

[Uzi2019]

Hi @Anonymous 

If you want to create relationship then you need to have calculated column.

try below dax 

Min CVN = CALCULATE(MIN(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN]))

max CVN = CALCULATE(MAX(Table3[CVN]), ALLEXCEPT(Table3, Table3[RN]))

I hope this may resolved your issue!

[Uzi2019 · Accepted solution]

Hi @Anonymous 

Do you want to have a seperate table??? 

Like this??

then take new table from modeling tab then write this 

Summarize table = SUMMARIZE(Table3,Table3[RN],
"Min CNV",CALCULATE(MIN(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])),
"Max CNV",CALCULATE(Max(Table3[CVN]),ALLEXCEPT(Table3, Table3[RN])))

I hope I answered your question!