topic Re: PowerQuery transform rows in Power Query

PowerQuery transform rows

coffeexyz — Fri, 04 Apr 2025 15:56:34 GMT

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!

Re: PowerQuery transform rows

lbendlin — Fri, 04 Apr 2025 21:08:18 GMT

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

Re: PowerQuery transform rows

rohit1991 — Tue, 08 Jul 2025 06:11:29 GMT

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.

Re: PowerQuery transform rows

v-ssriganesh — Mon, 07 Apr 2025 04:25:15 GMT

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.