Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
You sir are brilliant.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |