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

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.