Forum Discussion

ptewary's avatar
ptewary
Frequent Visitor
7 years ago
Solved

Consolidate multiple columns to two columns

Hi there,   I have a dataset like the following:   Recipe Ingredient 1 Ingredient 2 Ingredient 3 Ingredient 4 Measure 1 Measure 2 Measure 3  Measure 4 R1 I11 I12 I13 I14 M11 M...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    ptewary 

     

    Try this Custom Column. Then expand it to new rows

    Please see attached file with your data and steps

     

    =let 
ingredients=List.Select(Table.ColumnNames(#"Changed Type"),each Text.StartsWith(_,"Ingredient")),
measures=List.Select(Table.ColumnNames(#"Changed Type"),each Text.StartsWith(_,"Measure"))
in
#table({"Ingredients","MEASURES"},
List.Zip({
Record.ToList(
Record.SelectFields(_,ingredients)),
Record.ToList(
Record.SelectFields(_,measures))}))

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

ptewary 

 

Try this Custom Column. Then expand it to new rows

Please see attached file with your data and steps

 

=let 
ingredients=List.Select(Table.ColumnNames(#"Changed Type"),each Text.StartsWith(_,"Ingredient")),
measures=List.Select(Table.ColumnNames(#"Changed Type"),each Text.StartsWith(_,"Measure"))
in
#table({"Ingredients","MEASURES"},
List.Zip({
Record.ToList(
Record.SelectFields(_,ingredients)),
Record.ToList(
Record.SelectFields(_,measures))}))

 

ptewary's avatar
ptewary
Frequent Visitor
7 years ago

Thanks a lot - works perfectly!