cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matthewjd24
Frequent Visitor

Power Query - Split partially shipped PO line into two rows (shipped and not shipped)

Hello, I have made a little example in Excel to help explain what I'm looking for. I have a table of PO lines, their $ values, etc. But I need to categorize them into completed and not completed. What I'd like to do is split PO lines that have been partially completed into one PO line that is complete, and one that isn't. You can see in the top example that you would think we have $200 left to make, when really we have $30 left. It also tells you that we've made $100 so far when we've really made $270. Is this possible? Thank you.

'Category' and 'Value' are calculated columns. So it's just the first 3 columns that I really need to create a new row for.

matthewjd24_0-1674497971507.png

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

Hi @matthewjd24 

You can put the following code to Advanced Editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0gBHOOfnFqSlgnoFSrE60khFMxgKI/QtS85DkjGFylmhysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO line" = _t, #"Total Qty" = _t, #"Qty Shipped" = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO line", Int64.Type}, {"Total Qty", Int64.Type}, {"Qty Shipped", Int64.Type}, {"Category", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Total Qty]-[Qty Shipped]),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), {{"Custom", Splitter.SplitTextByPositions({0, 100}, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> 0)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Category] = "Closed" or [Custom] <> null then [Qty Shipped] else if [Custom] = null then [Total Qty]-[Qty Shipped] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Customs", each if [Custom.1] = [Qty Shipped] then [Custom.1] else 0),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if [Custom.1] = [Customs] then "Closed" else "Open"),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each ([Value]/[Total Qty])*[Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Total Qty", "Qty Shipped", "Category", "Value", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Total Qty"}, {"Customs", "Qty Shipped"}, {"Custom.2", "Category"}, {"Custom.3", "Value"}})
in
    #"Renamed Columns"

Output:

vxinruzhumsft_0-1674612602838.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

Hi @matthewjd24 

You can put the following code to Advanced Editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0gBHOOfnFqSlgnoFSrE60khFMxgKI/QtS85DkjGFylmhysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO line" = _t, #"Total Qty" = _t, #"Qty Shipped" = _t, Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO line", Int64.Type}, {"Total Qty", Int64.Type}, {"Qty Shipped", Int64.Type}, {"Category", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Total Qty]-[Qty Shipped]),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), {{"Custom", Splitter.SplitTextByPositions({0, 100}, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Custom", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> 0)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Category] = "Closed" or [Custom] <> null then [Qty Shipped] else if [Custom] = null then [Total Qty]-[Qty Shipped] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Customs", each if [Custom.1] = [Qty Shipped] then [Custom.1] else 0),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if [Custom.1] = [Customs] then "Closed" else "Open"),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each ([Value]/[Total Qty])*[Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Total Qty", "Qty Shipped", "Category", "Value", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Total Qty"}, {"Customs", "Qty Shipped"}, {"Custom.2", "Category"}, {"Custom.3", "Value"}})
in
    #"Renamed Columns"

Output:

vxinruzhumsft_0-1674612602838.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

You sir are brilliant.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.