Consolidate multiple columns to two columns

[ptewary]

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	M12	M13	M14
R2	I21	I22			M21	M22
R3	I31	I32	I33	I34	M31	M32	M33	M34
R4	I41	I42	I43		M41	M42	M43
R5	I51	I52	I53		M51	M52	M53

I want there to be just a single column for ingredients and a single column for the corresponding measures, like below:

Recipe	Ingredient	Measure
R1	I11	M11
R1	I12	M12
R1	I13	M13
R1	I14	M14
R2	I21	M21
R2	I22	M22
R3	I31	M31
R3	I32	M32
R3	I33	M33
R3	I34	M34
R4	I41	M41
R4	I42	M42
R4	I43	M43
R5	I51	M51
R5	I52	M52
R5	I53	M53

How can I achieve this in Power Query? Please let me know if any other clarification is needed.

Thanks in advance!

[mussaenda]

Measure is from data or you created it using DAX?

[ptewary]

---

@mussaenda wrote:

Measure is from data or you created it using DAX?

---

The Measure columns are from the data itself. The data is basically like: Ingredient 1 = "Salt", Measure 1 = "1 Tbsp"; Ingredient 2 = "Chicken", Measure 2 = "1 whole"; etc...

[Zubair_Muhammad · Accepted solution]

@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]

Thanks a lot - works perfectly!