Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mckrsb
Regular Visitor

A table which has values from table1 and all values from table2 which are not related.

I have two tables as below:
Cust

custosuppo
7a
7b
8a


Supp

Suppo
a
b
c


I want to table/matrix visual as follows:
t1 = 

custosuppo
7a
7b
8a


t2 = 

custosuppo
7c
8b
8c

 

both tables are related by suppo column. How do i create t2 visual?

1 REPLY 1
lbendlin
Super User
Super User

In Power Query this would be the approach

t2  query:

 

let
    Source = Table.SelectColumns(t1,{"custo"}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Supp),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Suppo"}, {"Suppo"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"custo", "Suppo"}, t1, {"custo", "suppo"}, "t1", JoinKind.LeftAnti),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"custo", "Suppo"})
in
    #"Removed Other Columns"

 

in DAX it's easier as you can use EXCEPT

 

t2_DAX = except(crossjoin(values(t1[custo]),values(Supp[Suppo])),t1)

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.