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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ahuhn
Helper I
Helper I

merge filtered tables

I have one table

 

I created two filtered tables based on this table, called T1 and T2

 

I need to compare specific values between T1 and T2, so I want to merge them based on common productID.

 

Reading the documentation, I should be able to 

- edit queries

- merge queries

 

Unfortunately, the two tables I made, T1 and T2, do not appear. The only table avaiable to manipulate is the original one

 

Am I missing something?

1 ACCEPTED SOLUTION

@ahuhn Before pivoting, please Change the DataType of "StatusLevel" to "WholeNumber" and give a try...





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

9 REPLIES 9
PattemManohar
Community Champion
Community Champion

@ahuhn I can understand that you have created two filtered tables T1 and T2 in "Data" pane using DAX. Then you are trying to merge these two tables in "Power Query" (but unfortunately you can't see them). 

 

It is because, you can't see the calculated tables and fields that are created in "Data" pane (using DAX) in Power Query Editor. 

 

You need to either do the filter tables T1 and T2 in Power Query itself and then Merge, or you need to merge in DAX itself.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Thank you for the useful comments. To add to the confusion, I am new to powerBI.

 

I have (+80 extra columns):

productstatusLeveldate
11201809
21201809
31201809
11201810
21201810
32201810
41201810

 

I am subsetting on date for T1 and T2, to obtain two tables.

 

I want to merge T1 and T2 in product so that I can accurately compare the statusLevel change bewteen the two dates.

 

I cannot find a merge function in DAX, nor find a way to create a filtered table in the power query.

 

Any suggestions are welcome

 

Anonymous
Not applicable

Hi @ahuhn,

 

Could you please send sample for T1 and T2.

 

 

T1:

productstatusLeveldate
11201809
21201809
31201809

 

T2:

productstatusLeveldate
11201810
21201810
32201810
41201810

 

I want to merge to get:

productstatusLevel201809statusLevel201810
111
211
312
4 1

 

@ahuhn Please try "Pivot" option on the subset table (just with the required columns you are interested as mentioned),

 

Just try "Pivot" for Date field on the new table (trimmed version) in Power Query and the output will be as expected.. Not required to have two tables and then merge...

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Love that idea!

 

Unfortunately, I get the following error:

 

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type

@ahuhn Before pivoting, please Change the DataType of "StatusLevel" to "WholeNumber" and give a try...





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi @ahuhn,

 

If you create summarized table or filtered table, you are right you can not find into edit queries that is default from power bi side.

Although if you want to merger those table for some visuals so there is alternative to do so.

 

As you mentioned both table have common id so just join that table in Relatonships tab on that id and use as you want.

 

Best Regards,

Ravi

Anonymous
Not applicable

Or just share some sample data or pbix file. which two column you want to compare.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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