Forum Discussion

quinnjohnson's avatar
quinnjohnson
Helper I
1 year ago
Solved

Aggregate table where a singular value from one column has multiple values in a second column

Hello,   I am wondering if it is possible to reorganize a table visual through some sort of method as follows;   The current table looks like this when I add the data to the table visual; ...
Need Help
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    1 year ago

    quinnjohnson 

    Here is a Power Query solution if you like:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Company Name"}, {{"Detail", each Table.TransformColumns( Table.AddIndexColumn(_,"i",1,1),{"i", each "Group" & Text.From(_)} ) }}),
    Custom1 = Table.Combine(#"Grouped Rows"[Detail]),
    #"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[i]), "i", "Group", List.Sum)
in
    #"Pivoted Column"

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the reply from Fowmy, please allow me to provide another insight.
Hi quinnjohnson ,

You can also use DAX to create the target table.
1. Create a calculated column indexed by company name.

IndexByCompanyName = 
ROWNUMBER(ORDERBY('Table'[Group]),PARTITIONBY('Table'[Company Name]))


2.Use the following DAX to create the target calculated table.

Preferred Table = 
SUMMARIZECOLUMNS('Table'[Company Name], 
"Group1",CALCULATE(MAX('Table'[Group]),'Table'[IndexByCompanyName]=1),
"Group2",CALCULATE(MAX('Table'[Group]),'Table'[IndexByCompanyName]=2),
"Group3",CALCULATE(MAX('Table'[Group]),'Table'[IndexByCompanyName]=3),
"Group4",CALCULATE(MAX('Table'[Group]),'Table'[IndexByCompanyName]=4)
)


3. Use this calculated table to create a table visual. Hopefully it will meet your needs.


Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

quinnjohnson's avatar
quinnjohnson
Helper I
1 year ago

Thank you for this explanation. it would work great if that was my real dataset however the actual data i am working with will have hundreds if not thousands of rows of data so the manual line by line Dax will not be feasible unfortunately.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi quinnjohnson ,

    So did Fowmy's solution solve your problem?

    To use his solution.
    Please open the Power Query editor and insert the code in the advanced editor of the imported table query.


    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • quinnjohnson's avatar
      quinnjohnson
      Helper I
      1 year ago

      this is how my advanced editor currently looks but i do not understand what parts of Fowmy's code i need to insert into my advanced editor or where to insert it. would it be possible copy and paste his code into what mine currently shows so that i can test? thank you!

       

      let
      Source = Sql.Database("MGAReport", "ITDev", [Query="--#(tab)declare @Date date = '1/1/2024'#(lf)#(lf)select distinct#(lf) --Dept#(tab)#(tab)#(tab)#(tab)#(tab)= 'BRO'#(lf)-- C.CompanyName#(lf) CompanyLocationName#(tab)= CLoc.LocationName#(lf)--, UnderwritingName#(tab)#(tab)= CLoc.[Name]#(lf), L.LineName#(lf)--, Q.StateID#(lf)--, PromotionalProgName#(tab)= ISNULL(PP.PromotionalProgName, 'Not Otherwise Classified')#(lf)--, Q.CompanyCompositeCommission#(lf)--, CLC.CompanyCommNew#(lf)--, CLC.CompanyCommRenewal#(lf)--, BoundPolicyCOunt#(tab)#(tab)= SUM(case when DateBound IS NOT NULL then 1 else 0 end)#(lf)--, QuotedPolicyCOunt#(tab)#(tab)= SUM(case when DateBound IS NULL then 1 else 0 end)#(lf)--, CompanyContact#(tab)#(tab)= ISNULL(CC.FName + ' ', '') + ISNULL(CC.LName, '')#(lf)--#(tab)#(tab)select top 15 *#(lf)from#(lf)Appalachian_IMS.dbo.tblQuotes Q#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyLocations CLoc#(lf)#(tab)on Q.CompanyLocationGuid = CLoc.CompanyLocationGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.lstLines L#(lf)#(tab)on Q.LineGUID = L.LineGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyLines CLin#(lf)#(tab)on Q.CompanyLineGuid = CLin.CompanyLineGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.tblUsers UU#(lf)#(tab)on UU.UserGUID = Q.UnderwriterUserGuid#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanies C#(lf)#(tab)on CLoc.CompanyGUID = C.CompanyGUID#(lf)inner join#(lf)[ITDev].[dbo].[Infrastructure_GroupMap_Users] B#(lf)#(tab)on B.UserID = UU.UserID#(lf)#(tab)and B.UserGroupID = 1#(lf)inner join#(lf)Appalachian_IMS.dbo.tblQuoteDetails QD#(lf)#(tab)on Q.QuoteGUID = QD.QuoteGuid#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyContacts CC#(lf)#(tab)on CC.CompanyContactGUID = QD.CompanyContactGuid#(lf)inner join#(lf)appalachian_IMS.dbo.appalachian_tblQuotes AQ #(lf)#(tab)#(tab)#(tab)#(tab)on Q.QuoteID = AQ.QuoteID#(lf)inner join#(lf)(#(lf)select CLC.CompanyLineID, Effective, CompanyCommNew, CompanyCommRenewal#(lf)from Appalachian_IMS.dbo.tblCompanyLineCommissions CLC#(lf)inner join#(lf)(#(lf)select CompanyLineID, EffDate = MAX(Effective)#(lf)from Appalachian_IMS.dbo.tblCompanyLineCommissions#(lf)group by CompanyLineID#(lf)) M#(lf)#(tab)on CLC.CompanyLineID = M.CompanyLineID and CLC.Effective = M.EffDate#(lf)) CLC#(lf)#(tab)on CLin.CompanyLineID = CLC.CompanyLineID#(lf)left join#(lf)Appalachian_IMS.dbo.Appalachian_lstPromotionalPrograms PP#(lf)#(tab)on AQ.PromotionalProgID = PP.PromotionalProgID#(lf)#(lf)where 1=1#(lf)--and ISNULL(Q.EndorsementNum, 0) = 0#(lf)--and DateBound IS NOT NULL#(lf)--and EffectiveDate >= @Date#(lf)--and Q.PolicyNumber = '22N45300AA0P85' #(lf)#(lf)#(lf)order by CLoc.LocationName, LineName", CreateNavigationProperties=false])
      in
      Source

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi quinnjohnson ,


        The following is a combination of the M code provided by Fowmy. You should be able to use it directly.

        let
Source = Sql.Database("MGAReport", "ITDev", [Query="--#(tab)declare @Date date = '1/1/2024'#(lf)#(lf)select distinct#(lf) --Dept#(tab)#(tab)#(tab)#(tab)#(tab)= 'BRO'#(lf)-- C.CompanyName#(lf) CompanyLocationName#(tab)= CLoc.LocationName#(lf)--, UnderwritingName#(tab)#(tab)= CLoc.[Name]#(lf), L.LineName#(lf)--, Q.StateID#(lf)--, PromotionalProgName#(tab)= ISNULL(PP.PromotionalProgName, 'Not Otherwise Classified')#(lf)--, Q.CompanyCompositeCommission#(lf)--, CLC.CompanyCommNew#(lf)--, CLC.CompanyCommRenewal#(lf)--, BoundPolicyCOunt#(tab)#(tab)= SUM(case when DateBound IS NOT NULL then 1 else 0 end)#(lf)--, QuotedPolicyCOunt#(tab)#(tab)= SUM(case when DateBound IS NULL then 1 else 0 end)#(lf)--, CompanyContact#(tab)#(tab)= ISNULL(CC.FName + ' ', '') + ISNULL(CC.LName, '')#(lf)--#(tab)#(tab)select top 15 *#(lf)from#(lf)Appalachian_IMS.dbo.tblQuotes Q#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyLocations CLoc#(lf)#(tab)on Q.CompanyLocationGuid = CLoc.CompanyLocationGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.lstLines L#(lf)#(tab)on Q.LineGUID = L.LineGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyLines CLin#(lf)#(tab)on Q.CompanyLineGuid = CLin.CompanyLineGUID#(lf)inner join#(lf)Appalachian_IMS.dbo.tblUsers UU#(lf)#(tab)on UU.UserGUID = Q.UnderwriterUserGuid#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanies C#(lf)#(tab)on CLoc.CompanyGUID = C.CompanyGUID#(lf)inner join#(lf)[ITDev].[dbo].[Infrastructure_GroupMap_Users] B#(lf)#(tab)on B.UserID = UU.UserID#(lf)#(tab)and B.UserGroupID = 1#(lf)inner join#(lf)Appalachian_IMS.dbo.tblQuoteDetails QD#(lf)#(tab)on Q.QuoteGUID = QD.QuoteGuid#(lf)inner join#(lf)Appalachian_IMS.dbo.tblCompanyContacts CC#(lf)#(tab)on CC.CompanyContactGUID = QD.CompanyContactGuid#(lf)inner join#(lf)appalachian_IMS.dbo.appalachian_tblQuotes AQ #(lf)#(tab)#(tab)#(tab)#(tab)on Q.QuoteID = AQ.QuoteID#(lf)inner join#(lf)(#(lf)select CLC.CompanyLineID, Effective, CompanyCommNew, CompanyCommRenewal#(lf)from Appalachian_IMS.dbo.tblCompanyLineCommissions CLC#(lf)inner join#(lf)(#(lf)select CompanyLineID, EffDate = MAX(Effective)#(lf)from Appalachian_IMS.dbo.tblCompanyLineCommissions#(lf)group by CompanyLineID#(lf)) M#(lf)#(tab)on CLC.CompanyLineID = M.CompanyLineID and CLC.Effective = M.EffDate#(lf)) CLC#(lf)#(tab)on CLin.CompanyLineID = CLC.CompanyLineID#(lf)left join#(lf)Appalachian_IMS.dbo.Appalachian_lstPromotionalPrograms PP#(lf)#(tab)on AQ.PromotionalProgID = PP.PromotionalProgID#(lf)#(lf)where 1=1#(lf)--and ISNULL(Q.EndorsementNum, 0) = 0#(lf)--and DateBound IS NOT NULL#(lf)--and EffectiveDate >= @Date#(lf)--and Q.PolicyNumber = '22N45300AA0P85' #(lf)#(lf)#(lf)order by CLoc.LocationName, LineName", CreateNavigationProperties=false])

#"Grouped Rows" = Table.Group(Source, {"Company Name"}, {{"Detail", each Table.TransformColumns( Table.AddIndexColumn(_,"i",1,1),{"i", each "Group" & Text.From(_)} ) }}),
    Custom1 = Table.Combine(#"Grouped Rows"[Detail]),
    #"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[i]), "i", "Group", List.Sum)
in
    #"Pivoted Column"

         
        If this doesn't work, please provide a screenshot of the error.

        Best Regards,
        Dengliang Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.