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 want to create Table 1 (see below) and eventually join it with Table 2.
I want to create table 1 with the following fields:
- Dates from 1/1/2019 - 12/31/2019
- Severities
- Project Key (290 project keys)
Where I could start: I have all 3 of the folllowing fields, but in separate tables. I want to merge them all into a table to look like the photo above.
I expect this table to be ginormous. The reason for this table is to perform a join with Table 2, so that I can get empty (null values) in a new table (see below)
Reason being is so that I can change those null values to a zero and compute a calculation.
Please help!
Solved! Go to Solution.
@Anonymous ,
Refer this post:
Thanks,
Tejaswi
Hi @Anonymous ,
Please let me know if these steps works for you:
1. Hit on new table( in the modeling) and enter this formulas( (or you can simply hit on enter data and enter the values you want)
- Table 1 : ( I am adding blank columns so that I can merge it with table 2, since table 2 has 4 columns)
Table 1 = Union( Row("Date","1/1/2019","severity","sev1","project key","a"," ",""), Row("Date","1/1/2019","severity","sev2","project key","a"," ",""), Row("Date","1/1/2019","severity","sev3","project key","a"," ",""), Row("Date","1/1/2019","severity","sev4","project key","a"," ",""), Row("Date","1/1/2019","severity","sev1","project key","b"," ",""), Row("Date","1/1/2019","severity","sev2","project key","b"," ",""))
-Table 2:
Table2 = Union( Row("Date","1/1/2019","severity","sev1","project key","a","Product","1"), Row("Date","1/1/2019","severity","sev3","project key","a","Product","1"), Row("Date","1/1/2019","severity","sev4","project key","a","Product","2"))
-Table 3-( Merge Table 1 and Table 2)
Just add table 2 after Union in your Table 1 formula.This will merge the data you need.
Table 1 = Union( Table2, Row("Date","1/1/2019","severity","sev1","project key","a"," ",""), Row("Date","1/1/2019","severity","sev2","project key","a"," ",""), Row("Date","1/1/2019","severity","sev3","project key","a"," ",""), Row("Date","1/1/2019","severity","sev4","project key","a"," ",""), Row("Date","1/1/2019","severity","sev1","project key","b"," ",""), Row("Date","1/1/2019","severity","sev2","project key","b"," ",""))
- Here is what I got as an output:
Highlighted sev 2 has Product key null. same as what you expected.
Let me know for any question,
Thanks,
Tejaswi
I already have the tables for Date, Severity, and Project Key.
I just need a method to join all 3 together.
@Anonymous
May be you can use Merge queries in the query editor.
Thanks,
Tejaswi
Can I merge (join) if they do not share a common primary key?
@Anonymous ,
Refer this post:
Thanks,
Tejaswi
Hello @Anonymous
You can use the CROSSJOIN function to generate a table. Something like.
Big Table = VAR Severities = DATATABLE("Severity",STRING,{{"Severity 1 - Critical"},{"Severity 2 - Major"},{"Severity 3 - Minor"},{"Severity 4 - Trivial"}}) VAR FirstJoin = CROSSJOIN ( Severities, VALUES('Projects'[Project Key])) RETURN CROSSJOIN (FirstJoin,Dates[date])
thank you this worked.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |