Forum Discussion

JamesMidgley's avatar
JamesMidgley
Icon for Advocate I rankAdvocate I
8 years ago
Solved

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

I need to be able to convert two existing columns and create a new table.     Any guidance greatly appreciated
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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