Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have dissimilar data sources with one column of like values, and I'm unsure how to join them; I may be asking the wrong question in my searches for 'combine', 'summarize', etc.
Source 1
Device | Firewall Status | Irrelevant Data |
COMPUTER1 | Off | Apple |
COMPUTER2 | On | Orange |
COMPUTER3 | On | Lemon |
Source 2
Computer Name | Antivirus Status |
COMPUTER1 | True |
COMPUTER4 | False |
Source 3
Hostname | Internet Facing | Other Data |
COMPUTER2 | Fully | null |
COMPUTER3 | Partially | Yes |
COMPUTER5 | Partially | null |
Desired Output (A matrix table)
AllComputers | Internet Facing | Firewall | Antivirus |
COMPUTER1 | null | Off | True |
COMPUTER2 | Fully | On | null |
COMPUTER3 | Partially | On | null |
COMPUTER4 | null | null | False |
COMPUTER5 | Partially | null | null |
I've tried to achieve this via Merge as New, OUTER JOIN in Transform Data, but the result is multiple 'Computername' type columns. My situation is similar like the post below - but adapting the UNION solution returns, 'a table of multiple values was supplied where a single value was expected'
What am I missing? Any help appreciated!
Solved! Go to Solution.
you can do this in pq.
pls see the attachment below
you can also use DAX to create a table
Table =
VAR tbl=DISTINCT(UNION(SELECTCOLUMNS('Source 1',"AllComputers",'Source 1'[Device]),SELECTCOLUMNS('Source 2',"Allcomputers",'Source 2'[Computer Name]),SELECTCOLUMNS('Source 3',"Allcomputers",'Source 3'[Hostname])))
return ADDCOLUMNS(tbl,"Internet Facing",maxx(FILTER('Source 3',[AllComputers]='Source 3'[Hostname]),'Source 3'[Internet Facing]),"firewall",maxx(FILTER('Source 1',[AllComputers]='Source 1'[Device]),'Source 1'[Firewall Status]),"Antivirus",maxx(FILTER('Source 2',[AllComputers]='Source 2'[Computer Name]),'Source 2'[Antivirus Status]))
Proud to be a Super User!
Thank you @ryan_mayu, your attachment contained the answer I needed;
For future searchers, I created a New Source / Blank Query, and input:
= Table.FromList( List.Distinct(List.Combine({#"Source 1"[Device],#"Source 2"[Computer Name],#"Source 3"[Hostname]})))
This produced a single column of all computers from the three sources.
Then, merge in each of the original sources (left join on computer name) to produce the desired output.
Thank you @ryan_mayu!
you are welcome
Proud to be a Super User!
you can do this in pq.
pls see the attachment below
you can also use DAX to create a table
Table =
VAR tbl=DISTINCT(UNION(SELECTCOLUMNS('Source 1',"AllComputers",'Source 1'[Device]),SELECTCOLUMNS('Source 2',"Allcomputers",'Source 2'[Computer Name]),SELECTCOLUMNS('Source 3',"Allcomputers",'Source 3'[Hostname])))
return ADDCOLUMNS(tbl,"Internet Facing",maxx(FILTER('Source 3',[AllComputers]='Source 3'[Hostname]),'Source 3'[Internet Facing]),"firewall",maxx(FILTER('Source 1',[AllComputers]='Source 1'[Device]),'Source 1'[Firewall Status]),"Antivirus",maxx(FILTER('Source 2',[AllComputers]='Source 2'[Computer Name]),'Source 2'[Antivirus Status]))
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |