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 and AlB 

 

At outset apologies for the delayed reply.  Thank you so much for your incredible command on the Power Query Platform.  You both are just superstar.

 

Jimmy801 your solution worked just perfectly as it was expected.  AlB  Your solution was also based on great approach.  But in reality with the education level of our users it was slightly advanced.  They don't want to learn the beauty of using PowerBI but just want to stick to the excel as they are all old school folks.

 

I just have 2 more queries in the solution proposed by Jimmy801  and then I am happy to accept the solution and mark it complete and give it thumbs ups.

 

1)  I copied the code to the blank query in the advanced editor window and ran it.  It worked just fine but in the power query view it shows blank cells as null value.  I just want to fill them with number 0.  How can that be made possible?

 

2)  How can I add a drop down based filter to choose the quarter and then the results can be changed accordingly.  In other words, each quarter has 3 months so if we can store month names in one of the column and then we can use it in our query as the table column head.

 

Thank you to answer these 2 queries and then I will close it.

Thank you so much for your help.

 

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