cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nilesh_amrutkar
Frequent Visitor

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

Items1P1F1A2P2F2A3P3F3A
a01-Jan05-Jan15-Jan01-Jan01-Jan01-Jan01-Jan01-Jan01-Jan
b02-Jan07-Jan16-Jan02-Jan02-Jan02-Jan02-Jan02-Jan02-Jan
c03-Jan09-Jan17-Jan03-Jan03-Jan03-Jan03-Jan03-Jan03-Jan
d04-Jan11-Jan18-Jan04-Jan04-Jan04-Jan04-Jan04-Jan04-Jan
e05-Jan13-Jan19-Jan05-Jan05-Jan05-Jan05-Jan05-Jan05-Jan

Report 1

ItemsStatus123
aP01-Jan01-Jan01-Jan
aF05-Jan01-Jan01-Jan
aA15-Jan01-Jan01-Jan
bP02-Jan02-Jan02-Jan
bF07-Jan02-Jan02-Jan
bA16-Jan02-Jan02-Jan
3 ACCEPTED SOLUTIONS
AnkitBI
Solution Sage
Solution Sage

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.

View solution in original post

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"

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@nilesh_amrutkar 

 

Fix errorReplacement in Table.ReplaceErrorValues.

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
AnkitBI
Solution Sage
Solution Sage

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.

View solution in original post

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

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"

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

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

@nilesh_amrutkar 

 

Fix errorReplacement in Table.ReplaceErrorValues.

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@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

 

 

Hi @nilesh_amrutkar  Great. Do Mark it as Solution as your issue is resolved.

 

Thanks

Ankit Jain

Hi @nilesh_amrutkar 

 

I can see in your code, you have used "Table.Unpivot" , whereas In my code I have mentioned "Table.UnpivotOtherColumns". Try changing that and try.

 

Thanks

Ankit Jain

 

Hi @AnkitBI 

 

Tried Unpivot other columns as well, but even after that same error.

 

Thanks,

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors