PowerQuery transform rows

[coffeexyz]

Hi, 
I'm currently try to transform a datatable in PowerQuery, the format of the data is similar to this format

ITEM #	Standard UOM	Alt UOM	Factor (Standard to ALT)
ITEM 1	BOX	EA	a
ITEM 1	BOX	FT2	b
ITEM 1	BOX	OZ	c
ITEM 1	BOX	PAL	d
ITEM 2	BUCKET	IN3	e
ITEM 2	BUCKET	GAL	f
ITEM 2	BUCKET	LB	g

Are there ways to transform this data into the following format, without splitting each ALT UOM into each individual tables? 

ITEM #	ALT UOM convert to ALT UOM
ITEM 1	EA to FT2
ITEM 1	EA to OZ
ITEM 1	EA to PAL
ITEM 1	FT2 to PAL
ITEM 1	FT2 to OZ
ITEM 2	IN3 to GAL
ITEM 2	IN3 to LB
ITEM 2	GAL to LB

Many thanks for the advise!

[v-ssriganesh]

Hi @coffeexyz,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @rohit1991 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[rohit1991 · Accepted solution]

Hi @coffeexyz ,
Yes, you can achieve this transformation in Power Query without having to manually split the Alt UOMs into separate tables. What you need is a self-join approach, where you join the table to itself based on matching ITEM #, then filter out combinations where the Alt UOM values are the same or already repeated in reverse.

This method allows you to generate all possible unique pairings of Alt UOMs per item and display them in the format you’re aiming for (e.g., "EA to FT2", "FT2 to PAL", etc.). After the self-join, you can use a conditional column or custom column to format the output string (e.g., [Alt UOM] & " to " & [Alt UOM.1]), then remove unnecessary columns. This way, you dynamically generate all conversions per item without needing separate tables for each UOM, keeping your transformation clean and scalable. Let me know if you'd like the exact Power Query steps or M code for this logic.

[lbendlin]

there is no discernible logic to your transformation request.  Please provide some more detail on how you get to the expected result.