Replace UNION/SUMMARIZE with M code ?

Hi,

In the Query Editor, select the first 3 columns > right click and select Unpivot other columns.  Right click on the Attribute column > Split column > By delimiter.  Specify the delimiter as _.  Rename the columns.  Click on Close and Apply.

Hope this helps.

Hi Ashish -

That technique worked for the 4 columns where the UoM is in the column header, but not for the ones where the value and UoM are in different columns.  However, starting with your suggestion I did the following:

- Copied the original query into a second query.

- On the original query, removed the columns that have quantity and UoM separately

- Unpivoted the 4 column with UoM in the header, then split the "Attribute" column on "_" (as you had said)

- In the 2nd query, removed the columns unpivoted previously

- Merged the Quantity and UoM columns with "=" (3 separate merge operations)

- Unpivoted the new merged columns

- Split the "Value" column on "="

- Split the "Attibute" column on "_" and removed the second column (values in that column were now UoM1, UoM2, UoM3)

- Appended the second query onto the first

Thanks for getting me pointed in the right direction!

David