My input data has 4 columns.
- PO Number ( Purchase Order Number)
- MR Number ( Material Requisition Number)
- Engg Progress ( Engineering Progress)
- Overall Progress
PO number column has unique entries. Multiple MR number can assigned to each PO Number. These MR numbers are separated by comma in column.
I want to create an individual row for each MR Number against respective PO Number
Table Transformation.xlsx (11.5 KB)
.
e.g. 7500084570 || MR-036,MR-038 || 50%|| 40%
Output
7500084570 || MR-036 || 50%|| 40%
7500084570 || MR-038 || 50%|| 40%
Kindly help with the query.
Thanks in advance
Hi @prafullchavan1
No problem. There are several transformation needed to provide output you want.
First - I highly recommend in Excel to define name since the inputs are in the middle + lots of other columns to avoid further transformations. For Demo purposes I just pick your Input data and call in Input
For that in Excel - go to Formula → Define Name
(in normal cases I will refer to a much larger range and add in Refer to larger scale of rows but for demo purposes)
With that step it’s much easier - just pick input
PowerQuery transformation
- Split MR number by Delimiter
In your case delimiter is comma
- Select MR number.1-3 column and unpivot only selected columns
Result after that:
*Value Rename to MR Number, Remove Attribute column
- Insert Custom Column PO-ID
After that additional Percentage Formating was needed to get desired Output
PBIX files + Excel with define name option:
Table transformation_for_prafullchavan1.pbix (20.3 KB)
Table Transformation v1.xlsx (11.6 KB)
Hope it helps.
Best regards,
Maja
PBIX version with Excel Sheet (without define name option) - with additional steps that were needed:
Table transformation_for_prafullchavan1_v2.pbix (20.7 KB)
Thank You for your help.
I need unpivot selected columns dynamic in nature.
Because number of MR per each PO are not fixed in my actual dataset. It may be greater than 3.
Hi Prafulla,
it sounds as if you simply want to split into rows:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"MR Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "MR Number")
in
#"Split Column by Delimiter"
Go for Each occurence and under Advanced options select Rows:
Output:
Regards,
Matthias
