Forum Discussion
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
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.
2 Replies
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
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.
