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! 🙂

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhNKcpX0lEyNAUSjsFKsTpoYl6OYDGf0sxikJA5QplX/uHFYGVmOMSgWp0Ti3LywZotEAp9E/NKU3PQLEYVBGmPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Code", type text}}),
    Partition = Table.Group(#"Changed Type", {"Name"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Age", "Code", "Index"}, {"Age", "Code", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each "Code "&Number.ToText([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Code")
in
    #"Pivoted Column"

Hope this helps.