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

[matthewjd24]

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.

[v-xinruzhu-msft · Accepted solution]

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:

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.

[matthewjd24]

You sir are brilliant.