Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable
7 REPLIES 7
Anonymous
Not applicable

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:

Capture 1.PNG

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

 

Let me know for any question,

 

Thanks,

Tejaswi

Anonymous
Not applicable

table6.PNGtable7.PNG

 

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

 

I just need a method to join all 3 together.

Anonymous
Not applicable

@Anonymous 

 

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

Capture 3.PNG

Thanks,

Tejaswi

Anonymous
Not applicable

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

Anonymous
Not applicable

jdbuchanan71
Super User
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])
Anonymous
Not applicable

thank you this worked.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.