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

[kedarsk]

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.

  

[Jimmy801]

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

Jimmy

[kedarsk]

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]

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 · Accepted solution]

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

[AlB]

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 

[kedarsk]

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

Thank you for your support guys @AlB & @Jimmy801 .

[Jimmy801]

Helllo @kedarsk 

here the solution for your table. In one step i filter for "Q4-20". This step you have to change whenever you want to evaluate another quarter. Be aware that in this case the last expand-step has also to be modified

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"})




in
    #"Expanded AllRows"

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

Jimmy

[AlB]

@kedarsk 

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 

[kedarsk]

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

[AlB]

@kedarsk 

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