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.

Highlighted
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

Frequent Visitor

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

thank you this worked.

Announcements

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

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: 165 members 1,930 guests
Recent signins: