Forum Discussion

kedarsk's avatar
kedarsk
New Member
5 years ago
Solved

Help needed to create query to display the table data in specific format

Hello all   I am a sales manager by profession hence not as much technical as much you guys are.  I need to publish the weekly status update about all the opportunities in my funnel.  Hence, I have...
Need Help
Show and Tell
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello 

     

    about your question 1... use this code to replace all null to 0

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZZNU9swEIb/iiZnmFhftnRMgc6009CUQC+UgzBq0CSRGVkO5d9XkmWKiYM8FB9ijxLryb67766uryd4cjT5vlj4G5/CKcpQ5h7Fq8stnUv7WJk1+KyMfBSbjVuClCB3my39R2nVTrqHH+Q4bHFe7eT2VprJzdH1hLQU6m5sClGHuXt9ubXl8hs4kcaq36oU1u+IKPTrsyHKqSz/UYqWAv3PiykqOsyf/etFQAtpfldmK3QpwbzSylZG6ZUPj+R+p4svPTA8Rn71s7w1jTBPAcwiGH0QGEEyDswjGKfBLftC3oFLKbZgVteyrrdSWw/MYe5fu5oPEefClPcBB7PIIy0PJ3lDVYMyjseFFzLpefQ/eIjRw2q+iI22rLC/FzLJOlUrZcUGLNVKC9sYvy/EGRkomqFqhXkEtlUDeRJ4VUsDzrRVVj2BT/Je7FTVmH7tYBb2GOb3PAmjXVBbPDBP8heiXEsLlvfiwe1xNKEY53zfmnvSoigt9iSHSUe6lGVjlH0Cs0dhpHaVCi6NUNrFCPweDB8OsicyitbEeYsm6ayeL0GH93qigrER1sDRGpi1oPdZg3B0sNX1koejMzAfi5tLI9YKXF3OQ5lSn/35VVJAHHNHSACNqNIurllZ+rydVNqaKhiR8GyMlCTGRlFApok/lbGNM+IzWYvNk1Vl7fNHMA7W/pYuljzmkHkwSfbxn81GO1Fv1cZX6lxosZKxoeKMUz6uQotuamW+RPEUw/crTHNODhvje2mrZ/NnedfKi4Cl72qtsMhHhgm7JgBDwGhEWocMwouB0THY3TCKPO6/c6KydB31EtqfkBlGeGBEorZ4vzZaRizusPADsDQbSSUdNW+p6D3qckb96/OzIX9+FfrfXCaxrSLIAw+m+8+ZvnuolLbgVFrpNq80+NVkGcrBhawfKl17GKY8Gz0+Y4IRbmNOV/DCqJ3ayJU7AznrVI37Mz3XFiwvRg/P2KRQ6FLuSAt9ooMcScn3fIuJE2Jf+Zjm2YNRm0jtDimhfXvqG1C3+uXSzTKzU+6EeSrrdT9clOMg3RixWXdYYP473p7h3wAPebeA7PB5+mV5oSzScHhg0/+QNmfhsDo7T6YU0W6Ecxr6/3hqb4ST8KeHx80rIPwIIMqC+1PAm78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sr#" = _t, OPPID = _t, Date = _t, #"Client Name" = _t, #"Solution Area" = _t, Value_BHD = _t, #"Sales Rep Name" = _t, #"Opportunity Status" = _t, #"Probable Quarter Closure" = _t, #"Probable Month Closure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr#", Int64.Type}, {"OPPID", type text}, {"Date", type text}, {"Client Name", type text}, {"Solution Area", type text}, {"Value_BHD", Int64.Type}, {"Sales Rep Name", type text}, {"Opportunity Status", type text}, {"Probable Quarter Closure", type text}, {"Probable Month Closure", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Probable Quarter Closure] = "Q4-20")),
    #"Removed Columns" = Table.SelectColumns(#"Filtered Rows",{"Value_BHD", "Sales Rep Name", "Probable Month Closure"}),
    ReformatYourGroupedTable = (tbl)=> 
    let 
        ReGroup = Table.Group(tbl, {"Probable Month Closure"}, {{"Sum", each List.Sum(_[#"Value_BHD"])}, {"Count", each List.Count(_[#"Value_BHD"])}}),
        UnpivotOther = Table.UnpivotOtherColumns(ReGroup, {"Probable Month Closure"}, "Attribute", "Value"),
        MergeColumns = Table.CombineColumns(UnpivotOther,{"Probable Month Closure", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        PivotColumn = Table.Pivot(MergeColumns, List.Distinct(MergeColumns[Merged]), "Merged", "Value", List.Sum)
    in 
        PivotColumn, 
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Sales Rep Name"}, {{"AllRows", each  ReformatYourGroupedTable(_)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"November Sum", "November Count", "December Sum", "December Count", "October Sum", "October Count"}, {"November Sum", "November Count", "December Sum", "December Count", "October Sum", "October Count"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded AllRows",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Expanded AllRows"))




in
    #"Replaced Value"

     

    about question 2 - in excel you can make a dropdown (using a table) where you put all possible quarters. Then you load this table in power query and drill down on the first cell in order the query deliveres a text. Then you can include this text like so

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Probable Quarter Closure] = YourQueryNameOfDropDown)),

     

    about question 3 - this is the way power bi translates it when you are entering manual data. You have to replace it with your real query

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

kedarsk's avatar
kedarsk
New Member
5 years ago

Hi Jimmy801

 

Just a list question for you, I did go through the query step by step but I was unable to figure out how did that decompress code came in the picture.  Was it result of the automated coding by Power Query or you inserted the lines by manual coding.  and if the latter is the case then please explain, how I can also achieve the same.   

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello 

 

about your question 1... use this code to replace all null to 0

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZZNU9swEIb/iiZnmFhftnRMgc6009CUQC+UgzBq0CSRGVkO5d9XkmWKiYM8FB9ijxLryb67766uryd4cjT5vlj4G5/CKcpQ5h7Fq8stnUv7WJk1+KyMfBSbjVuClCB3my39R2nVTrqHH+Q4bHFe7eT2VprJzdH1hLQU6m5sClGHuXt9ubXl8hs4kcaq36oU1u+IKPTrsyHKqSz/UYqWAv3PiykqOsyf/etFQAtpfldmK3QpwbzSylZG6ZUPj+R+p4svPTA8Rn71s7w1jTBPAcwiGH0QGEEyDswjGKfBLftC3oFLKbZgVteyrrdSWw/MYe5fu5oPEefClPcBB7PIIy0PJ3lDVYMyjseFFzLpefQ/eIjRw2q+iI22rLC/FzLJOlUrZcUGLNVKC9sYvy/EGRkomqFqhXkEtlUDeRJ4VUsDzrRVVj2BT/Je7FTVmH7tYBb2GOb3PAmjXVBbPDBP8heiXEsLlvfiwe1xNKEY53zfmnvSoigt9iSHSUe6lGVjlH0Cs0dhpHaVCi6NUNrFCPweDB8OsicyitbEeYsm6ayeL0GH93qigrER1sDRGpi1oPdZg3B0sNX1koejMzAfi5tLI9YKXF3OQ5lSn/35VVJAHHNHSACNqNIurllZ+rydVNqaKhiR8GyMlCTGRlFApok/lbGNM+IzWYvNk1Vl7fNHMA7W/pYuljzmkHkwSfbxn81GO1Fv1cZX6lxosZKxoeKMUz6uQotuamW+RPEUw/crTHNODhvje2mrZ/NnedfKi4Cl72qtsMhHhgm7JgBDwGhEWocMwouB0THY3TCKPO6/c6KydB31EtqfkBlGeGBEorZ4vzZaRizusPADsDQbSSUdNW+p6D3qckb96/OzIX9+FfrfXCaxrSLIAw+m+8+ZvnuolLbgVFrpNq80+NVkGcrBhawfKl17GKY8Gz0+Y4IRbmNOV/DCqJ3ayJU7AznrVI37Mz3XFiwvRg/P2KRQ6FLuSAt9ooMcScn3fIuJE2Jf+Zjm2YNRm0jtDimhfXvqG1C3+uXSzTKzU+6EeSrrdT9clOMg3RixWXdYYP473p7h3wAPebeA7PB5+mV5oSzScHhg0/+QNmfhsDo7T6YU0W6Ecxr6/3hqb4ST8KeHx80rIPwIIMqC+1PAm78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sr#" = _t, OPPID = _t, Date = _t, #"Client Name" = _t, #"Solution Area" = _t, Value_BHD = _t, #"Sales Rep Name" = _t, #"Opportunity Status" = _t, #"Probable Quarter Closure" = _t, #"Probable Month Closure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sr#", Int64.Type}, {"OPPID", type text}, {"Date", type text}, {"Client Name", type text}, {"Solution Area", type text}, {"Value_BHD", Int64.Type}, {"Sales Rep Name", type text}, {"Opportunity Status", type text}, {"Probable Quarter Closure", type text}, {"Probable Month Closure", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Probable Quarter Closure] = "Q4-20")),
    #"Removed Columns" = Table.SelectColumns(#"Filtered Rows",{"Value_BHD", "Sales Rep Name", "Probable Month Closure"}),
    ReformatYourGroupedTable = (tbl)=> 
    let 
        ReGroup = Table.Group(tbl, {"Probable Month Closure"}, {{"Sum", each List.Sum(_[#"Value_BHD"])}, {"Count", each List.Count(_[#"Value_BHD"])}}),
        UnpivotOther = Table.UnpivotOtherColumns(ReGroup, {"Probable Month Closure"}, "Attribute", "Value"),
        MergeColumns = Table.CombineColumns(UnpivotOther,{"Probable Month Closure", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
        PivotColumn = Table.Pivot(MergeColumns, List.Distinct(MergeColumns[Merged]), "Merged", "Value", List.Sum)
    in 
        PivotColumn, 
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Sales Rep Name"}, {{"AllRows", each  ReformatYourGroupedTable(_)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"November Sum", "November Count", "December Sum", "December Count", "October Sum", "October Count"}, {"November Sum", "November Count", "December Sum", "December Count", "October Sum", "October Count"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded AllRows",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Expanded AllRows"))




in
    #"Replaced Value"

 

about question 2 - in excel you can make a dropdown (using a table) where you put all possible quarters. Then you load this table in power query and drill down on the first cell in order the query deliveres a text. Then you can include this text like so

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Probable Quarter Closure] = YourQueryNameOfDropDown)),

 

about question 3 - this is the way power bi translates it when you are entering manual data. You have to replace it with your real query

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy