Merging two tables

[Mangnuel]

Ey Power-Bi world,

I need help please 😊

Topic or Task: Merge / Combining two tables with given rules

I need to combine two different tables.
First table looks like this

Date, Asset-Code and a few Values

second table looks like this

pretty much the same, just the Date, Asset-code and only one Value,
Both tables include the yield of the asset, but from different sources.
The utility is always a few months behand the SCADA-Readings.

Goal ist to merge the to tables, but just as far as we have utility values. Rest from the first table is not included or will follow as soon as we have the data from the officials (Utility).



Thanks in adavance.

The final table is needed for my measures. So al calculations have the same amount of data and no months are missing.

[gmsamborn · Accepted solution]

Hi @Mangnuel 

In this case, you should be able to use “Merge Queries as New”.

In Power Query:

1. Select your Table1 and in the Combine section of the Home menu, select Merge Queries As New.
2. In the Merge window, select Table 2 as the second table.  Select the common columns in each table ( Date and Asset-Code ) using the CTRL key, and hit OK.
3. In the Merge1 table, expand the Table 2 column, and select ONLY Yield Utility and hit OK.
4. In the new Yield Utility column, filter out the null values.

Let me know if you have any questions.

[Anonymous · Accepted solution]

Thanks for the reply from tharunkumarRTK and gmsamborn , please allow me to provide another insight: 
Hi  @Mangnuel ,

Here are the steps you can follow：

1. Create calculated table.

Table =
ADDCOLUMNS(
    'Table 1',
    "Yield Utility",
    SUMX(
        FILTER(ALL('Table 2'),
    'Table 2'[Date]=EARLIER('Table 1'[Date])&&
    'Table 1'[Asset-Code]=EARLIER('Table 1'[Asset-Code])),[Yield Utility]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mangnuel]

Problem is,

First Tabel, Daily resolution.
Seconde Tabel, monthly resolution.
So I only have one value on the first day of the month beeing the monthly total.

[gmsamborn]

OK.

First, I can't think of an easy way to do it in DAX.  Not saying there isn't a way.

Second, if you have a date table you should use either a month-name column or preferably a Yr-Mon column instead of the date column.

Does this help?

Also, can you show the expected result from your example data to verify numbers?

(also best would be a pbix with sample data)

[gmsamborn · Accepted solution]

Hi @Mangnuel 

In this case, you should be able to use “Merge Queries as New”.

In Power Query:

1. Select your Table1 and in the Combine section of the Home menu, select Merge Queries As New.
2. In the Merge window, select Table 2 as the second table.  Select the common columns in each table ( Date and Asset-Code ) using the CTRL key, and hit OK.
3. In the Merge1 table, expand the Table 2 column, and select ONLY Yield Utility and hit OK.
4. In the new Yield Utility column, filter out the null values.

Let me know if you have any questions.

[Mangnuel]

Hi @gmsamborn 

it works perfectly, thanks a lot.
Everyhting looks good!

But one further questions.
Is it also possible within Power Bi by using DAX-Formulars?

Thanks again

[tharunkumarRTK]

@Mangnuel 

Though I wont recommend creating DAX cacculated tables and columns, I am providing some possibilities for education purposes.

Yes you can do the same with DAX.

1. create a caculated column in both Table 1 and Table 2. The column definition should be, a concatenation of YearMonth column and Asset-Code column. (Ex: Jan-24 | A).

(Lets assume you named your new column as a Join_Key)

2. You can use DAX join functions perform the merge operation.

https://www.sqlbi.com/articles/using-join-functions-in-dax/

Please be informed, you can also merge tables with out creating a new calcualted column, I am suggesting you to do so as it would be simpler to understand. The join functions in DAX can also be used without relationships, but in that case, the join condition is applied to columns with the same data lineage regardless of the column name or the same name and no data lineage.

https://www.sqlbi.com/articles/replacing-relationships-with-join-functions-in-dax 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!