create new table by creating new columns based upon values in existing column

[JamesMidgley]

I need to be able to convert two existing columns and create a new table.

Any guidance greatly appreciated

[Ashish_Mathur · Accepted solution]

Hi,

The following M code will solve the problem

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mode", type text}, {"Outcome", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Outcome", "Outcome - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Outcome - Copy"]), "Outcome - Copy", "Outcome")
in
    #"Pivoted Column"

Hope this helps.

[v-shex-msft]

Hi @JamesMidgley,

You can refer to below formula to achieve your requirement.

Summary = 
    SUMMARIZE( 
        'Sample',[Mode],
        "A",IF(CONTAINS(FILTER('Sample','Sample'[Mode]=EARLIER([Mode])),'Sample'[Outcome],"a"),"a"),
        "B",IF(CONTAINS(FILTER('Sample','Sample'[Mode]=EARLIER([Mode])),'Sample'[Outcome],"b"),"b"),
        "C",IF(CONTAINS(FILTER('Sample','Sample'[Mode]=EARLIER([Mode])),'Sample'[Outcome],"c"),"c"),
        "D",IF(CONTAINS(FILTER('Sample','Sample'[Mode]=EARLIER([Mode])),'Sample'[Outcome],"d"),"d")
    )

Regards,

Xiaoxin Sheng