cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danieljkim17 Regular Visitor
Regular Visitor

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.

table1.PNG

 

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)

 

Table2.PNG

 

Reason being is so that I can change those null values to a zero and compute a calculation.

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
tejaswidmello Established Member
Established Member

Re: Alternative to merging large table - Need null values in a table

7 REPLIES 7
Highlighted
Super User
Super User

Re: Alternative to merging large table - Need null values in a table

Hello @danieljkim17 

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])
tejaswidmello Established Member
Established Member

Re: Alternative to merging large table - Need null values in a table

Hi @danieljkim17 ,

 

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:

Capture 1.PNG

Highlighted sev 2 has Product key null. same as what you expected.

 

Let me know for any question,

 

Thanks,

Tejaswi

danieljkim17 Regular Visitor
Regular Visitor

Re: Alternative to merging large table - Need null values in a table

table6.PNGtable7.PNG

 

I already have the tables for Date, Severity, and Project Key.

 

I just need a method to join all 3 together.

tejaswidmello Established Member
Established Member

Re: Alternative to merging large table - Need null values in a table

@danieljkim17 

 

May be you can use Merge queries in the query editor.

Capture 3.PNG

Thanks,

Tejaswi

danieljkim17 Regular Visitor
Regular Visitor

Re: Alternative to merging large table - Need null values in a table

Can I merge (join) if they do not share a common primary key?

tejaswidmello Established Member
Established Member

Re: Alternative to merging large table - Need null values in a table

danieljkim17 Regular Visitor
Regular Visitor

Re: Alternative to merging large table - Need null values in a table

thank you this worked.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)