cancel
Showing results for
Did you mean:
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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

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

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

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

Frequent Visitor

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

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

I just need a method to join all 3 together.

Established Member

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

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

Thanks,

Tejaswi

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?

Established Member

Highlighted
Frequent Visitor

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

thank you this worked.

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,278 guests
Recent signins: