Power Query, Pivot every alternate row

[chriscorpion786]

Hi ,
In the custom column, I have store names and every alternate row contains the word T&F, I need to pivot the rows where T&F along with the "Yes/No" in the value column.

The end result should read:

Custom	Value	T&F
Dubai Mall-DM1-1404	Yes	No

Much appreciated for a solution in Power Query, file is attached.

Thanks,
Mustafa

 

[chriscorpion786]

Hi ,
In the custom column, I have store names and every alternate row contains the word T&F, I need to pivot the rows where T&F along with the "Yes/No" in the value column.

The end result should read:

Custom	Value	T&F
Dubai Mall-DM1-1404	Yes	No

View attachment 90375
Much appreciated for a solution in Power Query, file is attached.

Thanks,
Mustafa

 

[chriscorpion786]

By the way, I have posted the same question in Mr.Excel forum as per the link below:

Power Query , pivot every alternate row

Hi All, I have store names and every alternate row contains the word T&F. I need to pivot every alternate row which contains T&F along with the value "Yes/No" in the adjacent column. The end result show look like the below table: CustomValueT&F Dubai Mall-DM1-1404YesNo DCC -...

www.mrexcel.com

 

[Frank2021]

Hi,

Here you are

If you like it, don't forget to click the thumbs up

 

[chriscorpion786]

Thanks Frank, it worked. Great!
The Combined step is where you have used a custom function, could you explain that, bit difficult for me to decipher it.
Is there no UI method through Power Query that we can do this ?

 

[Frank2021]

Hello,

Starting with the user interface is perfectly normal, but it’s important to realize how powerful the M language can be for processing and modeling data. Some tasks are either very complex or simply not possible through the interface alone, so I encourage you to get comfortable with M.

I’m sharing a new, simpler solution that still involves some M code, but it’s easier to follow and I’m confident you’ll understand it without any issues. I’ve also included a detailed step-by-step explanation. I hope this helps!

Best Regards

1. Load the table
Source: Loads your Excel table named "Table1" into Power Query.

2. Convert all columns to text
ChangeType: Makes sure all columns are treated as text (avoids number/date issues).

3. Unpivot columns
Unpivot: Converts columns (except product details) into rows:

• Attribute: Name of the original column (e.g., "T&F", "Dubai Mall-DM1- 1404", etc.)
• Value: Value from the original cell

4. Add a 'Custom' column to tag T&F
AddCustom: Adds a new column called "Custom". If the attribute name contains "T&F", the value is "T&F"; otherwise, it keeps the attribute name.

5. Remove the old 'Attribute' column
RemoveAttr: Deletes the "Attribute" column—now you use "Custom".

6. Reorder columns for clarity
Reorder: Rearranges columns for easier reading.

7. Add an index
AddIdx: Adds a unique row number (index) to each row, starting from 1.

8. Add a group number
AddGrp: Adds a "Grp" column that groups every two rows together:

• Rows 1-2: Grp 0
• Rows 3-4: Grp 1
• and so on

9. Separate odd and even rows
Odd: Gets the odd-numbered rows (first row in each pair).
Even: Gets the even-numbered rows (second row in each pair).

10. Join odd and even rows by group
Join: Combines each odd row with its matching even row (same group number).

11. Expand the joined even row’s Value column
ExpandEven: Adds the "Value" from the even row to the odd row, names it "T&F".

12. Remove temporary columns
RemoveTmp: Deletes "Idx" and "Grp" columns, which you no longer need.

Result:
Each row now includes original info, the "Custom" column, the value from the odd row, and the "T&F" value from the paired even row

 

[chriscorpion786]

Brilliant & Super helpful. I went through each step line by line, debugging, understanding and learning at the same time from your file and your steps mentioned here.

Thank you Frank!