cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danieljkim17 Frequent Visitor
Frequent 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
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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent Visitor

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

thank you this worked.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 117 members 1,740 guests
Please welcome our newest community members: