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 | 2F | 2A | 3P | 3F | 3A |
| a | 01-Jan | 05-Jan | 15-Jan | 01-Jan | 01-Jan | 01-Jan | 01-Jan | 01-Jan | 01-Jan |
| b | 02-Jan | 07-Jan | 16-Jan | 02-Jan | 02-Jan | 02-Jan | 02-Jan | 02-Jan | 02-Jan |
| c | 03-Jan | 09-Jan | 17-Jan | 03-Jan | 03-Jan | 03-Jan | 03-Jan | 03-Jan | 03-Jan |
| d | 04-Jan | 11-Jan | 18-Jan | 04-Jan | 04-Jan | 04-Jan | 04-Jan | 04-Jan | 04-Jan |
| e | 05-Jan | 13-Jan | 19-Jan | 05-Jan | 05-Jan | 05-Jan | 05-Jan | 05-Jan | 05-Jan |
Report 1
| Items | Status | 1 | 2 | 3 |
| a | P | 01-Jan | 01-Jan | 01-Jan |
| a | F | 05-Jan | 01-Jan | 01-Jan |
| a | A | 15-Jan | 01-Jan | 01-Jan |
| b | P | 02-Jan | 02-Jan | 02-Jan |
| b | F | 07-Jan | 02-Jan | 02-Jan |
| b | A | 16-Jan | 02-Jan | 02-Jan |
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}})
10 Replies
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 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"