Summarized table without measure

[PBIfanatic]

Hi,

I have 2 tables shown below, they are related by ID(Account) and Account ID(Product)

Account
Account Name	ID	Country
Acc1	AC1	US
Acc2	AC2	US

Product
Product ID	Account ID	Owner	Type
PRD1	AC1	Mike	Type1
PRD1	AC1	Sam	Type2
PRD2	AC1	John	Type1
PRD2	AC1	Sara	Type2

And I want the output in a pivot table

	US	SG
	Owner (Type)	Owner (Type)
PRD1	Mike (Type1) Sam (Type2)
PRD2	John(Type1) Sara(Type2)

The data volume is close to a million in the accounts table so need to factor in performance as well. 

thanks for looking into this.

[Fowmy · Accepted solution]

@PBIfanatic 

You create the following measure:

Owner Type = 
IF(
    ISINSCOPE( 'Product'[Product ID]) && ISINSCOPE( 'Account'[Country]), 
    CONCATENATEX(
        SUMMARIZE('Product' ,'Product'[Owner] ,'Product'[Type] ),
        'Product'[Owner] & "(" & 'Product'[Type] &")" & UNICHAR(10)
    ) 
)

[PBIfanatic]

thanks @Fowmy  

Thats an elegant solution. Another new function learnt. 🙂

I am also looking forward to how this is done in Power query from @CNENFRNL 

[Fowmy]

@PBIfanatic 

Great!,
Go ahead and accept a solution if it works for you, this will help others who look for similar solutions to find it easily. 

[CNENFRNL]

Seems weird a SG column comes out of nowhere from your mock dataset.

Whatever, it's a simple transformation in PQ

let
    Account = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxONlTSUXJ0BpGhwUqxOmAxI7CYEUIMzI8PdgfSQCI2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, ID = _t, Country = _t]),
    Product = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCghyMVTSUXJ0BpG+mdmpQCqksiDVUClWB002ODEXKmkEkzSCS3rlZ+ShazVC0lqUiKI32D0eqAAsHx/sDqT98pPyUyqhaiKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Account ID" = _t, Owner = _t, Type = _t]),
    #"Combined Columns" = Table.CombineColumns(Product, {"Owner", "Type"}, each _{0}&" ("&_{1}&")", "Owner (Type)"),
    #"Grouped Rows" = Table.Group(#"Combined Columns", {"Product ID", "Account ID"}, {{"ar", each Text.Combine([#"Owner (Type)"], "#(lf)")}}),
    #"Merged Tables" = Table.NestedJoin(#"Grouped Rows", "Account ID", Account, "ID", "merged", JoinKind.LeftOuter),
    #"Expanded merged" = Table.RemoveColumns(Table.ExpandTableColumn(#"Merged Tables", "merged", {"Country"}, {"Country"}), "Account ID"),
    #"Pivoted Column" = Table.Pivot(#"Expanded merged", List.Distinct(#"Expanded merged"[Country]), "Country", "ar")
in
    #"Pivoted Column"

[PBIfanatic]

hi @CNENFRNL  

How do I get the grouping? When I try the Group by option in the Home tab,it doesnt allow me to do this without a measure.

   #"Grouped Rows" = Table.Group(#"Combined Columns", {"Product ID", "Account ID"}, {{"ar", each Text.Combine([#"Owner (Type)"], "#(lf)")}}),