Forum Discussion
Solved
Matrix Report
I am looking for Report 1 and my database is as per the format of Table 1. In Reality my stages are 13 here in sample database and report I have shown only 3. Table 1 Items 1P 1F 1A 2P 2...
Try below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1PVKzAMxTKEMQxgDIUU8I1YnWikJxDWCiZvDjDWDiRiRzgAZmwziGsPELWHGwsxHSBHPABmbAuKawEyDecfQAqbShHQGyNhUlCCF2WsIczZcigRGbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Items = _t, #"1P" = _t, #"1F" = _t, #"1A" = _t, #"2P" = _t, #"2F" = _t, #"2A" = _t, #"3P" = _t, #"3F" = _t, #"3A" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Items"}, "Attribute", "Value"), #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByRepeatedLengths(1), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Position", List.Distinct(#"Split Column by Position"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.Hi,
Paste this M code in the following window:
Home > Get Data > Blank Query > View > Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1PVKzAMxTKEMQxgDIUU8I1YnWikJxDWCiZvDjDWDiRiRzgAZmwziGsPELWHGwsxHSBHPABmbAuKawEyDecfQAqbShHQGyNhUlCCF2WsIczZcigRGbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Items = _t, #"1P" = _t, #"1F" = _t, #"1A" = _t, #"2P" = _t, #"2F" = _t, #"2A" = _t, #"3P" = _t, #"3F" = _t, #"3A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Items", type text}, {"1P", type date}, {"1F", type date}, {"1A", type date}, {"2P", type date}, {"2F", type date}, {"2A", type date}, {"3P", type date}, {"3F", type date}, {"3A", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Items"}, "Attribute", "Value"), #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Status"}}) in #"Renamed Columns"Fix errorReplacement in Table.
ReplaceErrorValues. = Table.ReplaceErrorValues(#"Changed Type1", {{"1P", null}, {"1F", null}, {"1A", null}, {"2P", null}, {"2F", null}, {"2A", null}})
Hi AnkitBI
Thanks for your response, it was quiet helpful, but still I am unable to achieve the intended result.
below is screenshot of the query from advanced editor.
let
Source = Excel.Workbook(File.Contents("\\180.190.16.30\jll\Proj_Mgmt\PCP\PCP Consolidated Updated.xlsm"), null, true),
#"PCP (Open Projects)_Sheet" = Source{[Item="PCP (Open Projects)",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"PCP (Open Projects)_Sheet", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Sr.N", Int64.Type}, {"Capex No", type text}, {"Capex Description", type text}, {"Capex Approval date", type date}, {"Original Mech completion", type date}, {"Forecast As per Master Tracker", type date}, {"Capex Value", type number}, {"Equipment / Items", type text}, {"As on date updates", type text}, {"DCI", type any}, {"Stage", type text}, {"Stage pending from Department", type text}, {"Responsible Person", type text}, {"Project Engineer", type text}, {"Location", type text}, {"SBU", type text}, {"Status", type text}, {"Package", type text}, {"Brief Technical Specification", type text}, {"Qty.", Int64.Type}, {"Unit", type text}, {"Budgeted cost ", type number}, {"Commited cost", type number}, {"Yet to Commit", type number}, {"Forecast of Cost at Completion", type number}, {"Lead time from Capex Approval to DS/BOQ", Int64.Type}, {"1. Data Sheet/ BOQ - Planned", type date}, {"1. Data Sheet/ BOQ - Forecast", type date}, {"1. Data Sheet/ BOQ - Actual", type date}, {"1. Data Sheet/ BOQ Delay Days", Int64.Type}, {"2. RFQ - Planned", type date}, {"2. RFQ - Forecast", type date}, {"2. RFQ - Actual", type date}, {"2. RFQ Delay Days", Int64.Type}, {"Query Received on", type text}, {"Query Resolved on", type text}, {"3.Final Offer- Planned", type date}, {"3.Final Offer- Forecast", type date}, {"3.Final Offer- Actual", type date}, {"3. Final Offer Delay Days", Int64.Type}, {"4. Offers to Design - Planned", type date}, {"4. Offers to Design - Forecast", type date}, {"4. Offers to Design - Actual", type date}, {"4. offers to design Delay Days", Int64.Type}, {"5. TBE - Planned", type date}, {"5. TBE - Forecast", type date}, {"5. TBE - Actual", type date}, {"5. TBE Delay Days", Int64.Type}, {"6. PR/TBE Submission - Planned", type date}, {"6. PR/TBE Submission - Forecast", type date}, {"6. PR/TBE Submission - Actual", type date}, {"6. PR/TBE Submission Delay Days", Int64.Type}, {"7. TA - Planned", type date}, {"7. TA - Forecast", type date}, {"7. TA - Actual", type date}, {"7. TA Delay Days", type any}, {"8. Order - Planned", type date}, {"8. Order - Forecast", type date}, {"8. Order - Actual", type date}, {"8. Order Delay Days", Int64.Type}, {"PO/JO No.", type any}, {"Vendor Name", type text}, {"Vendor Contact No.", type text}, {"9. Drg from Supplier - Planned", type date}, {"9. Drg from Supplier - Forecast", type date}, {"9. Drg from Supplier - Actual", type date}, {"9. Drg from Supplier Delay Days", Int64.Type}, {"10. Drg Approval - Planned", type date}, {"10. Drg Approval - Forecast", type date}, {"10. Drg Approval - Actual", type date}, {"10. Drg Approval Delay Days", Int64.Type}, {"11. Dispatch Clearance - Planned", type date}, {"11. Dispatch Clearance - Forecast", type date}, {"11. Dispatch Clearance - Actual", type date}, {"11. Dispatch Clearance Delay Days", Int64.Type}, {"12. Delivery - Planned", type date}, {"12. Delivery - Forecast", type date}, {"12. Delivery - Actual", type date}, {"12. Delivery Delay Days", Int64.Type}, {"Const Planned Start", type text}, {"Const Forecast Start", type text}, {"Const Actual Start", type text}, {"Const Delay Days in Start ", Int64.Type}, {"Const Planned Finish", type text}, {"Const Forecast Finish", type text}, {"Const Actual Finish", type text}, {"Const Delay Days in Finish", Int64.Type}, {"PO Planned", type any}, {"PO Forecast", Int64.Type}, {"PO Actual", type any}, {"Drg to Dly Planned", type any}, {"Drg to Dly Forecast", Int64.Type}, {"Drg to Dly Actual", type any}, {"Supply Planned", type any}, {"Supply Forecast", Int64.Type}, {"Suply Actual", type any}, {"Capex Status", type text}, {"Category", type text}, {"PCP Last Updated On", type date}, {"Schedule Status", type text}, {"Main List No", type text}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"1. Data Sheet/ BOQ - Planned", "1. Data Sheet/ BOQ - Forecast", "1. Data Sheet/ BOQ - Actual", "2. RFQ - Planned", "2. RFQ - Forecast", "2. RFQ - Actual", "3.Final Offer- Planned", "3.Final Offer- Forecast", "3.Final Offer- Actual", "4. Offers to Design - Planned", "4. Offers to Design - Forecast", "4. Offers to Design - Actual", "5. TBE - Planned", "5. TBE - Forecast", "5. TBE - Actual", "6. PR/TBE Submission - Planned", "6. PR/TBE Submission - Forecast", "6. PR/TBE Submission - Actual", "7. TA - Planned", "7. TA - Forecast", "7. TA - Actual", "8. Order - Planned", "8. Order - Forecast", "8. Order - Actual", "9. Drg from Supplier - Planned", "9. Drg from Supplier - Forecast", "9. Drg from Supplier - Actual", "10. Drg Approval - Planned", "10. Drg Approval - Forecast", "10. Drg Approval - Actual", "11. Dispatch Clearance - Planned", "11. Dispatch Clearance - Forecast", "11. Dispatch Clearance - Actual", "12. Delivery - Planned", "12. Delivery - Forecast", "12. Delivery - Actual", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Capex No] <> null)),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Filtered Rows", {"1. Data Sheet/ BOQ - Planned", "1. Data Sheet/ BOQ - Forecast", "1. Data Sheet/ BOQ - Actual", "2. RFQ - Planned", "2. RFQ - Forecast", "2. RFQ - Actual", "3.Final Offer- Planned", "3.Final Offer- Forecast", "3.Final Offer- Actual", "4. Offers to Design - Planned", "4. Offers to Design - Forecast", "4. Offers to Design - Actual", "5. TBE - Planned", "5. TBE - Forecast", "5. TBE - Actual", "6. PR/TBE Submission - Planned", "6. PR/TBE Submission - Forecast", "6. PR/TBE Submission - Actual", "7. TA - Planned", "7. TA - Forecast", "7. TA - Actual", "8. Order - Planned", "8. Order - Forecast", "8. Order - Actual", "9. Drg from Supplier - Planned", "9. Drg from Supplier - Forecast", "9. Drg from Supplier - Actual", "10. Drg Approval - Planned", "10. Drg Approval - Forecast", "10. Drg Approval - Actual", "11. Dispatch Clearance - Planned", "11. Dispatch Clearance - Forecast", "11. Dispatch Clearance - Actual", "12. Delivery - Planned", "12. Delivery - Forecast", "12. Delivery - Actual"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Only Selected Columns1", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"As soon as I unpivot the columns, data which is more than 1000 rows comes down to 89 rows and after "Pivoted column" I get below error - DataFormat.Error: Invalid cell value '#VALUE!'.
Thanks and Regards,
Nilesh Amrutkar
Fix errorReplacement in Table.
= Table.ReplaceErrorValues(#"Changed Type1", {{"1P", null}, {"1F", null}, {"1A", null}, {"2P", null}, {"2F", null}, {"2A", null}})
AnkitBI ; Ashish_Mathur ; v-chuncz-msft
Thanks a lot, after replacement of errors from all columns, solution worked and finally I have got the intended result.
Thanks and Regards,
Nilesh Amrutkar