Convert single row data into multiple lines based on Qty

[key3535]

Hi Guys
I am looking at the solution wherein the row data converted into multiple row based on Quantity. This is to map lot nos (unique codes) against each line item. Attached data and desired output in the file

Data:

Sales_Org

Payer

Name

Sold to

Name

Ship to

Name

Reference Invoice

Country

Invoice

Billing Type

Billing Date

Invoice Item

Item Category

PurchasePlan

Quantity

Sales Unit

Unit Price

Total Value

HSN Code

VAT

CGST

SGST/UGST

IGST

Max Retail Price

Rebate basis

Currency

Delivery

Delivery Item

Order Number

Order Item

Material

Material Description

Brand

Profit center

Standard Price

Currency

Price Unit

Base Unit

Standard Price in Total

List_Price

Currency

Price Unit

Uom

List Price in Total

8080​

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

IN

1101139898​

F2

12/2/2023​

140​

TAN

2​

6P

1169.3​

2,338.60​

90013000​

280.63​

0​

0​

280.63​

2,190.00​

2,338.60​

INR

4030773352​

140​

8643946269​

140​

16050292​

ACUVUE OASYS 8.4 -8.50 6P Rx

PH

10010​

388.08​

INR

1​

6P

776.16​

1,169.30​

INR

1​

6P

2,338.60​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1101139899​

F2

12/2/2023​

10​

TAN

3​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4030773342​

10​

2065656987​

10​

16177498​

1DM ASTIG 8.5 -1.25 -2.25/10 30P Rx

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1101139899​

F2

12/2/2023​

30​

TAN

3​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4030773342​

30​

2065656987​

30​

16089060​

1DM ASTIG 8.5 -8.00 -1.25/10 30P RX

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Output:

Sales_Org

Payer

Name

Sold to

Name

Ship to

Name

Reference Invoice

Country

Invoice

Billing Type

Billing Date

Invoice Item

Item Category

PurchasePlan

Quantity

Sales Unit

Unit Price

Total Value

HSN Code

VAT

CGST

SGST/UGST

IGST

Max Retail Price

Rebate basis

Currency

Delivery

Delivery Item

Order Number

Order Item

Material

Material Description

Brand

Profit center

Standard Price

Currency

Price Unit

Base Unit

Standard Price in Total

List_Price

Currency

Price Unit

Uom

List Price in Total

8080​

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

IN

1.101E+09​

F2

12/2/2023​

140​

TAN

1​

6P

1169.3​

2,338.60​

90013000​

280.63​

0​

0​

280.63​

2,190.00​

2,338.60​

INR

4.031E+09​

140​

8.644E+09​

140​

16050292​

ACUVUE OASYS 8.4 -8.50 6P Rx

PH

10010​

388.08​

INR

1​

6P

776.16​

1,169.30​

INR

1​

6P

2,338.60​

8080​

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

6329365​

S PHARMACEUTICALS

IN

1.101E+09​

F2

12/2/2023​

140​

TAN

1​

6P

1169.3​

2,338.60​

90013000​

280.63​

0​

0​

280.63​

2,190.00​

2,338.60​

INR

4.031E+09​

140​

8.644E+09​

140​

16050292​

ACUVUE OASYS 8.4 -8.50 6P Rx

PH

10010​

388.08​

INR

1​

6P

776.16​

1,169.30​

INR

1​

6P

2,338.60​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

10​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

10​

2.066E+09​

10​

16177498​

1DM ASTIG 8.5 -1.25 -2.25/10 30P Rx

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

30​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

30​

2.066E+09​

30​

16089060​

1DM ASTIG 8.5 -8.00 -1.25/10 30P RX

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

10​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

10​

2.066E+09​

10​

16177498​

1DM ASTIG 8.5 -1.25 -2.25/10 30P Rx

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

30​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

30​

2.066E+09​

30​

16089060​

1DM ASTIG 8.5 -8.00 -1.25/10 30P RX

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

10​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

10​

2.066E+09​

10​

16177498​

1DM ASTIG 8.5 -1.25 -2.25/10 30P Rx

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

8080​

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

6234682​

ABORN INDUSTRIES

IN

1.101E+09​

F2

12/2/2023​

30​

TAN

1​

30P

1286.77​

3,860.31​

90013000​

463.23​

0​

0​

463.23​

2,410.00​

3,860.31​

INR

4.031E+09​

30​

2.066E+09​

30​

16089060​

1DM ASTIG 8.5 -8.00 -1.25/10 30P RX

1MA

10031​

583.5​

INR

1​

30P

1,750.50​

1,286.77​

INR

1​

30P

3,860.31​

 

[ExcelToDAX]

Using Power Query and assuming your Excel table is called Table1, you could use the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "RepeatedRow", each {1..[Quantity]}),
    #"Expanded RepeatedRow" = Table.ExpandListColumn(#"Added Custom", "RepeatedRow"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded RepeatedRow",{"RepeatedRow"})
in
    #"Removed Columns"

 

[key3535]

not sure how to run this query