Forum Discussion
power query transpose
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! 🙂
Hi Anonymous
I've recreated your table like this:
In Query Editor, select column Code1 and Code 2 and hit Unpivot Columns in the Transform ribbon. This results in this:
Remove column Attribute and filterout the blanks in column Value and you have your desired result.
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! 🙂
Kind regards
Djerro123
Hi JarroVGIT
Thanks for trying to help me but....when you say "i've recreated your table..." this is how i'd like the result to be, you know? Maybe i wasn't explicit enough. So sorry about it.
I can't have any duplicates on names and i have to see for each name if there's an AS or a JA or both...like the table you've recreated.
Can you help anyhow? Thank you so much.
Pedro