Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello,
I'm trying to create a new table from 2 other tables without any relationship between them.
The goal is to add all distinct Computer name from the 2 tables and add some selected columns from them too.
After that I need to select some info from 1 column of 1 of the tables. Column names in tables can have the same column name or can be different.
My code works but I have more or less 15 other columns to add and I'm not sure my code is the best one ...
Can you help me to optimize it please ?
EVALUATE
VAR u = UNION (
SUMMARIZE ( Computer , computer[computer.cn]),
SUMMARIZE ( DevicesWithInventory , DevicesWithInventory[DeviceName2])
)
RETURN
SUMMARIZE (
u,computer[computer.cn],
"ClientType",IF(LEN(LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK()))=0,
LOOKUPVALUE(DevicesWithInventory[PCStatus],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),
LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK())),
"operatingSystem",IF(LEN(LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))=0,
LOOKUPVALUE(DevicesWithInventory[OperatingSystem],DevicesWithInventory[DeviceName2],[computer.cn],BLANK()),
LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK()))
)
hello all,
This query seems a little bit more efficient:
EVALUATE
VAR u = UNION (
SUMMARIZE ( computer , computer[computer.cn]),
SUMMARIZE ( DevicesWithInventory , DevicesWithInventory[DeviceName2])
)
RETURN
SUMMARIZE (
u,computer[computer.cn],
"Device ID",
VAR sComputer =LOOKUPVALUE(computer[Device ID],computer[computer.cn],[computer.cn],BLANK())
VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[Device ID],DevicesWithInventory[DeviceName2],[computer.cn],BLANK())
RETURN
SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer)
,
"ClientType",
VAR sComputer =LOOKUPVALUE(computer[ClientType],computer[computer.cn],[computer.cn],BLANK())
VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[PCStatus],DevicesWithInventory[DeviceName2],[computer.cn],BLANK())
RETURN
SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer),
"OperatingSystem",
VAR sComputer = LOOKUPVALUE(computer[computer.operatingSystem],computer[computer.cn],[computer.cn],BLANK())
VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[OperatingSystem],DevicesWithInventory[DeviceName2],[computer.cn],BLANK())
RETURN
SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer),
"PCStatus",
VAR sComputer =LOOKUPVALUE(computer[PCStatus],computer[computer.cn],[computer.cn],BLANK())
VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[Device state],DevicesWithInventory[DeviceName2],[computer.cn],BLANK())
RETURN
SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer),
"User ID",
/*VAR sComputer =LOOKUPVALUE(User[UserID],computer[computer.cn],[computer.cn],BLANK())*/
VAR sDeviceInventory = LOOKUPVALUE(DevicesWithInventory[UserID],DevicesWithInventory[DeviceName2],[computer.cn],BLANK())
RETURN
/*SWITCH(TRUE,LEN(sComputer)=0,sDeviceInventory,sComputer)*/
sDeviceInventory
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
11 | |
9 | |
8 | |
8 |