Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Good Morning,
I am trying to create a report for analyzing scrap data in my manufacturing plant. I like to start at a high level and be able to drill down for specific issues. Below is an example of the data.
Manufacturing is divided into different departments. Printing would be one of them. That department will define scrap in terms of categories such as Dirt, Scratches, Color, etc. That will all be added into an overall department scrap for Printing. This is done for all departments.
I would like to have a visual of Clustered Column Chart that shows scrap totals for each department. So I would have a column for Printing Scrap. I would like to click on that and be able to Drill Down to the sub categories of Dirt, Scratches and Color to see the breakdown.
If this is not possible, having another Clustered Column Chart on the same page that shows the respective sub categories when the department scrap column is clicked would also work.
Thank you for everyone’s time and support in advance.
Date | Work Order | Batch | Printing Scrap (Total) | Dirt | Scratches | Color |
1/1/2020 | 123 | 1 | 8 | 1 | 2 | 5 |
1/2/2020 | 123 | 2 | 14 | 5 | 5 | 4 |
1/2/2020 | 567 | 1 | 18 | 8 | 3 | 7 |
1/3/2020 | 567 | 2 | 16 | 8 | 2 | 6 |
Solved! Go to Solution.
Hi @hvora787
If you want to show sub-category under category when drilling down, please refer to my query below:
Table2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Rejects"}, {"Attribute.2", "sub_Rejects"}, {"Value", "sub_Rejects value"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, #"Table 2 (2)", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, "Table 2 (2)", JoinKind.FullOuter),
#"Expanded Table 2 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table 2 (2)", {"Rejects value"}, {"Table 2 (2).Rejects value"})
in
#"Expanded Table 2 (2)"
Table2(2)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Job Number", "Work Order", "Batch", "Date", "R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Columns", {{"Attribute", each Text.BeforeDelimiter(_, " "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute", "Rejects"}, {"Value", "Rejects value"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Rejects", Order.Ascending}})
in
#"Sorted Rows"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @hvora787
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
Best Regards
Maggie
Hi @hvora787
Is the table show the structure of your real data?
If not, could you show more information?
Best Regards
Maggie
Hi Maggie,
I have pasted some of the data below. I am not not familiar with the interfase for this board and could not find a way to attach the actual Excel file. Please let me know if this works for you.
R, N, I, C, M and P Rejects are scrap from different departments. R R1, R R2 and so on on the right side fo the table are sub-catagories of rejects from each departments. Please ignore the top row. When the HTML formatting is removed the top cells unmerged.
Please let me know if you have any questions. Thank you for your help.
Regards,
Hemal
Order Detail | Departments | Yield | DEPARTMENT SUB-CATAGORIES | |||||||||||||||||||||||||
Job Number | Work Order | Batch | Date | R Rejects | N Rejects | I Rejects | C Rejects | M Rejects | P Rejects | Good | QC Samples | Total Bad | Batch Size | Yield | Scrap % | R R1 | R R2 | N R1 | N R2 | I R1 | I R2 | I R3 | C R1 | C R2 | M R1 | M R2 | P R1 | P R2 |
11823 | 1913600 | 682 | 1/2 | 9 | 7 | 70 | 6 | 11 | 11 | 382 | 2 | 116 | 500 | 76% | 23% | 5 | 4 | 5 | 2 | 24 | 4 | 42 | 1 | 5 | 1 | 10 | 1 | 10 |
11823 | 1913600 | 683 | 1/2 | 4 | 6 | 44 | 10 | 9 | 14 | 409 | 2 | 89 | 500 | 82% | 18% | 1 | 3 | 5 | 1 | 16 | 3 | 25 | 5 | 5 | 2 | 7 | 5 | 9 |
11823 | 1913600 | 685 | 1/2 | 9 | 5 | 97 | 3 | 20 | 11 | 351 | 2 | 147 | 500 | 70% | 29% | 5 | 4 | 4 | 1 | 44 | 28 | 25 | 2 | 1 | 8 | 12 | 8 | 3 |
11823 | 1913600 | 689 | 1/2 | 4 | 4 | 31 | 10 | 19 | 9 | 419 | 2 | 79 | 500 | 84% | 16% | 3 | 1 | 2 | 2 | 2 | 3 | 26 | 5 | 5 | 12 | 7 | 7 | 2 |
11823 | 1913600 | 717 | 1/2 | 3 | 7 | 75 | 4 | 9 | 20 | 378 | 2 | 120 | 500 | 76% | 24% | 2 | 1 | 4 | 3 | 31 | 29 | 15 | 3 | 1 | 3 | 6 | 10 | 10 |
12165 | 1914152 | 540 | 1/2 | 8 | 9 | 55 | 4 | 12 | 15 | 393 | 2 | 105 | 500 | 79% | 21% | 4 | 4 | 4 | 5 | 1 | 31 | 23 | 3 | 1 | 10 | 2 | 9 | 6 |
12165 | 1914152 | 544 | 1/2 | 3 | 5 | 45 | 5 | 5 | 16 | 405 | 8 | 87 | 500 | 81% | 17% | 2 | 1 | 3 | 2 | 17 | 9 | 19 | 1 | 4 | 1 | 4 | 9 | 7 |
12165 | 1914152 | 567 | 1/2 | 10 | 8 | 113 | 4 | 19 | 6 | 336 | 2 | 162 | 500 | 67% | 32% | 5 | 5 | 5 | 3 | 40 | 50 | 23 | 2 | 2 | 10 | 9 | 2 | 4 |
12165 | 1914152 | 574 | 1/2 | 6 | 7 | 117 | 9 | 8 | 11 | 338 | 2 | 160 | 500 | 68% | 32% | 2 | 4 | 2 | 5 | 50 | 37 | 30 | 4 | 5 | 2 | 6 | 3 | 8 |
12165 | 1914152 | 585 | 1/2 | 2 | 6 | 65 | 3 | 11 | 8 | 401 | 2 | 97 | 500 | 80% | 19% | 1 | 1 | 5 | 1 | 20 | 15 | 30 | 1 | 2 | 2 | 9 | 5 | 3 |
12165 | 1914152 | 586 | 1/2 | 7 | 7 | 33 | 7 | 20 | 21 | 401 | 2 | 97 | 500 | 80% | 19% | 3 | 4 | 5 | 2 | 13 | 9 | 11 | 5 | 2 | 9 | 11 | 10 | 11 |
12165 | 1914152 | 591 | 1/2 | 6 | 6 | 102 | 7 | 12 | 9 | 354 | 2 | 144 | 500 | 71% | 29% | 4 | 2 | 2 | 4 | 39 | 36 | 27 | 5 | 2 | 8 | 4 | 5 | 4 |
12165 | 1914152 | 594 | 1/2 | 6 | 7 | 36 | 5 | 4 | 16 | 422 | 2 | 76 | 500 | 84% | 15% | 5 | 1 | 3 | 4 | 3 | 28 | 5 | 1 | 4 | 3 | 1 | 9 | 7 |
12165 | 1914152 | 595 | 1/2 | 6 | 5 | 100 | 6 | 7 | 3 | 369 | 2 | 129 | 500 | 74% | 26% | 1 | 5 | 4 | 1 | 2 | 50 | 48 | 4 | 2 | 3 | 4 | 2 | 1 |
12165 | 1914152 | 597 | 1/2 | 10 | 8 | 73 | 6 | 14 | 8 | 377 | 2 | 121 | 500 | 75% | 24% | 5 | 5 | 5 | 3 | 30 | 17 | 26 | 1 | 5 | 12 | 2 | 3 | 5 |
Hi @hvora787
If you want to show sub-category under category when drilling down, please refer to my query below:
Table2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Rejects"}, {"Attribute.2", "sub_Rejects"}, {"Value", "sub_Rejects value"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, #"Table 2 (2)", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, "Table 2 (2)", JoinKind.FullOuter),
#"Expanded Table 2 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table 2 (2)", {"Rejects value"}, {"Table 2 (2).Rejects value"})
in
#"Expanded Table 2 (2)"
Table2(2)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Job Number", "Work Order", "Batch", "Date", "R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Columns", {{"Attribute", each Text.BeforeDelimiter(_, " "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute", "Rejects"}, {"Value", "Rejects value"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Rejects", Order.Ascending}})
in
#"Sorted Rows"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie,
Thank you for providing the detailed response. This is of great help.
Regards,
Hemal
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |