Forum Discussion
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 few fields to consider namely sales rep name, opportunity name, opportunity created date, Proposal submitted date, value of the opportunity, expected quarter for closure and expected month for closure.
I already have imported all the above mentioned data in a simple power query by doing get and transform. but my challenge is how
to get the data in the following format. We need to publish weekly MIS in the following format.
Besides format there is one more concern, if the sales rep doesn't have any opportunity in that month of the quarter then the value should be considered as 0 as shown in the image below and his name should still remain there.
Any help will be much appreciated.
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
10 Replies
Hi kedarsk
Can you share some sample data of the tables you use in the report so that we can have a specific solution? Or, ideally, share the pbix (perhaps anonymized), or a mock pbix with dummy data that reproduces the problem
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 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
Hello kedarsk
as I don't know how your database is looking like, it's difficult to propose a suitable solution. What I did is to try to make a practicable example to show you an possible approach. I created a Custom function to transform a grouped table in order you can expand it the format you need. Check it out - but be aware that such a transformation for sure is not an easy task 🙂
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc47CkAhEEPRvUwtMgmItu+zC3H/21CYQgN24RSX9G6PJXNkeKavWWykjQyEiyKUoa8GmiqvCtFPC1WVV4Xor4XjL8qhi8cE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Value", Int64.Type}}, "de-DE"), #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Date", each Date.MonthName(_, "en-US"), type text}}), ReformatYourGroupedTable = (tbl)=> let ReGroup = Table.Group(tbl, {"Date"}, {{"Sum", each List.Sum(_[Value])}, {"Count", each List.Count(_[Value])}}), UnpivotOther = Table.UnpivotOtherColumns(ReGroup, {"Date"}, "Attribute", "Value"), MergeColumns = Table.CombineColumns(UnpivotOther,{"Date", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), PivotColumn = Table.Pivot(MergeColumns, List.Distinct(MergeColumns[Merged]), "Merged", "Value", List.Sum) in PivotColumn, #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Name"}, {{"AllRows", each ReformatYourGroupedTable(_)}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"October Sum", "October Count", "December Sum", "December Count", "November Sum", "November Count"}, {"October Sum", "October Count", "December Sum", "December Count", "November Sum", "November Count"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",{"Name", "October Sum", "October Count", "November Sum", "November Count", "December Sum", "December Count"}) in #"Reordered Columns"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
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
JimmyAt 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.
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.
