Forum Discussion
Help needed to create query to display the table data in specific format
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
Hi all
thank you very much for responding to my issue. I understand that most of you need the sample data to exactly understand the structure of my table. Please find link below to download the sample data file for your understanding.
https://1drv.ms/x/s!AhUykOTGN1SSpwDR-N-XAsO_MOQT?e=y69aZR
Kindly let me know if it was accessible to yourself or no.
Thank you
Take a look at the attached file for an idea. Bear in mind that I have made quite a few assumptions on the calculations for what is displayed so the numbers won't make much sense but this can give you an idea of how to get what you need. It would be advisable to have a date table. In any case, you will probably need to add some date-related columns to you current table as I have done in the example file. Don't mind the aesthetics of the report too much for now. That can be worked on later.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB
I appreciate the innovative approach you have brought on for the discussion. However, in our organization everyone is familiar with only excel. Hence, I need the similar thing in the format I initially posted as screenshot. You have provided extra functionality of drilling down to per quarter which is actually not required but we can consider it in case we decide to stick to 6 column format and then include quater as dropdown filter in excel just like Pivot Table.
I need the same thing in my excel file. How can I achieve that with Power Query and not the Power BI?
Please advise.
Thank you
Kedar
You can probably go with something like what Jimmy801 suggests and load the table into excel. You might need some additional tweaking on the Excel sheet itself.
Maybe it's the time to migrate to Power BI 😉
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers