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.