Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nilesh_amrutkar
Helper I
Helper I

Matrix Report

‎12-30-2019 04:59 AM

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
Labels:
Message 1 of 11
1,120 Views
0
3 ACCEPTED SOLUTIONS
AnkitBI
Solution Sage
Solution Sage

‎12-30-2019 05:36 AM

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

Message 2 of 11
1,099 Views
Ashish_Mathur
Super User
Super User
In response to nilesh_amrutkar

‎12-30-2019 04:38 PM

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

Message 4 of 11
1,059 Views
v-chuncz-msft
Community Support
Community Support
In response to nilesh_amrutkar

‎12-31-2019 12:31 AM

@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

Message 9 of 11
1,001 Views
10 REPLIES 10
AnkitBI
Solution Sage
Solution Sage

‎12-30-2019 05:36 AM

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.

Message 2 of 11
1,100 Views
nilesh_amrutkar
Helper I
Helper I
In response to AnkitBI

‎12-30-2019 04:23 PM

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

Message 3 of 11
1,039 Views
0
Ashish_Mathur
Super User
Super User
In response to nilesh_amrutkar

‎12-30-2019 04:38 PM

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/
Message 4 of 11
1,060 Views
AnkitBI
Solution Sage
Solution Sage
In response to Ashish_Mathur

‎12-30-2019 09:51 PM

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.

Message 5 of 11
1,009 Views
0
nilesh_amrutkar
Helper I
Helper I
In response to AnkitBI

‎12-30-2019 10:41 PM

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

Message 6 of 11
1,003 Views
0
v-chuncz-msft
Community Support
Community Support
In response to nilesh_amrutkar

‎12-31-2019 12:31 AM

@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.
Message 9 of 11
1,002 Views
nilesh_amrutkar
Helper I
Helper I
In response to v-chuncz-msft

‎12-31-2019 01:23 AM

@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

 

 

Message 10 of 11
968 Views
0
AnkitBI
Solution Sage
Solution Sage
In response to nilesh_amrutkar

‎12-31-2019 02:21 AM

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

 

Thanks

Ankit Jain

Message 11 of 11
695 Views
0
AnkitBI
Solution Sage
Solution Sage
In response to nilesh_amrutkar

‎12-30-2019 11:38 PM

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

 

Message 7 of 11
992 Views
0
nilesh_amrutkar
Helper I
Helper I
In response to AnkitBI

‎12-31-2019 12:58 AM

Hi @AnkitBI 

 

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

 

Thanks,

 

Message 8 of 11
972 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All