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;

 

I would like it to look like this instead;

 

 

Where in the preferred result, the columns are all the same value, but the results are grouped by the Company Name column and the values belonging to each Company Name is listed side by side 

 

Is this possible within Power BI? If there is a solution within SQL that could also help.

 

Thanks!

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"

     

     

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    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"

     

     

    • quinnjohnson's avatar
      quinnjohnson
      Helper I
      1 year ago

      Thank you! That is exactly what i need, i am just struggling on where exactly i type these lines of code at? i have barely worked with this type of coding within Power BI.

  • 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.