Forum Discussion
Alternative to merging large table - Need null values in a table
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
- Severity 1 - Critical
- Severity 2 - Major
- Severity 3 - Minor
- Severity 4 - Trivial
- 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!
Anonymous ,
Refer this post:
Thanks,
Tejaswi
7 Replies
- jdbuchanan71
Super User
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]) 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
