Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

power query transpose

Hi guys I'm trying to transpose this table but i can't do that. Maybe i'm not looking well. Can you guys give me help? The idea is to have this result... Name           Age      Code1  Code2 ...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    I created a calculated table for you like this:

    NewTable = 
VAR names = DISTINCT('Table'[Name])
RETURN
ADDCOLUMNS(
    SELECTCOLUMNS(names, "Names", [Name]), 
    "Age", CALCULATE(AVERAGE('Table'[Age]), FILTER('Table', 'Table'[Name] = [Names])),
    "Code1", IF(CONTAINS('Table', 'Table'[Name], [Names], 'Table'[Code], "AS"), "AS", BLANK()),
    "Code2", IF(CONTAINS('Table', 'Table'[Name], [Names], 'Table'[Code], "JA"), "JA", BLANK())
)

    If this doesn't work in your case, please explain in detail how PowerBI should know what value should be in Code1 and Code2 value. 

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

v-yuta-msft's avatar
v-yuta-msft
Community Support
6 years ago

Anonymous ,

 

That's much more simple. Click on column Code1 and Code2, then click Transform-> Unpivot Columns. Remove and Attribute column and rename Value column with "Code". Then remove the blank value in the Code column using filter as below:

 

 

Finally, you will achieve the table.

 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey v-yuta-msft 

I'm very sorry to say but you got it all wrong. The final solution that you've presented is what i have now, and what i want to transform. Could you please read my original/first message. You'll understand what i need...

Thank you so much for your effort

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    I created a calculated table for you like this:

    NewTable = 
VAR names = DISTINCT('Table'[Name])
RETURN
ADDCOLUMNS(
    SELECTCOLUMNS(names, "Names", [Name]), 
    "Age", CALCULATE(AVERAGE('Table'[Age]), FILTER('Table', 'Table'[Name] = [Names])),
    "Code1", IF(CONTAINS('Table', 'Table'[Name], [Names], 'Table'[Code], "AS"), "AS", BLANK()),
    "Code2", IF(CONTAINS('Table', 'Table'[Name], [Names], 'Table'[Code], "JA"), "JA", BLANK())
)

    If this doesn't work in your case, please explain in detail how PowerBI should know what value should be in Code1 and Code2 value. 

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    Hi, please see the previous solution I have post.

     

    Regards,

    Jimmy Tao