Forum Discussion

nilesh_amrutkar's avatar
nilesh_amrutkar
Helper I
6 years ago
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...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Hi nilesh_amrutkar  

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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"

     

  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    nilesh_amrutkar 

     

    Fix errorReplacement in Table.ReplaceErrorValues.

    = Table.ReplaceErrorValues(#"Changed Type1", {{"1P", null}, {"1F", null}, {"1A", null}, {"2P", null}, {"2F", null}, {"2A", null}})

     

AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

Hi nilesh_amrutkar  

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.

nilesh_amrutkar's avatar
nilesh_amrutkar
Helper I
6 years ago

Hi AnkitBI,

 

Could you please elaborate more on this? Where to and how to put this code? 

Do I need to create the new table?

 

Thanks and Regards,

Nilesh Amrutkar

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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"

     

    • AnkitBI's avatar
      AnkitBI
      Solution Sage
      6 years ago

      Hi nilesh_amrutkar 

       

      As ashish mentioned, you need to give it at Home > Get Data > Blank Query > View > Advanced Editor. You need to copy below code after your final step. Replace "YourSource" with final step name.

          #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(YourSource, {"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"

      If your existing final step is like below

          #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", Int64.Type}, {"B", Int64.Type}, {"C", type any}, {"D", Int64.Type}})
in
    #"Changed Type"

      Then it will become

          #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", Int64.Type}, {"B", Int64.Type}, {"C", type any}, {"D", Int64.Type}})
    #"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"

      If still facing issues, then share your existing Code from Advanced Editor.

       

      Thanks
      Ankit Jain
      Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

      • nilesh_amrutkar's avatar
        nilesh_amrutkar
        Helper I
        6 years ago

        Hi  AnkitBI 

        Ashish_Mathur 

        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