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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)