Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |