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 ,

 

In query editor, click on column [Code] then click Pivot using Values Column [Age] as below:

 

 Then add two conditional columns as below:

 

Finally, remove column [JA] and rename column [AS] with [Age]:

 

 

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

Hi v-yuta-msft 

Thank you very much for your reply but your solution starts with my desired solution....
Could you please think of a way of doing it, from my original table, from the 2nd table?

Thank you so much

Pedro