Forum Discussion
Merge two tables with different source using query
Hi, thank you for your response. I managed to make the second option working, but time was not reduced. At first it load the whole DB and after that executing filtering. As for the first option I had something similar, but there is still time problem. Is there a power app that could help me prepare data from sql, which could be then passed to powerbi? Or maybe some power app which would display data in the form of table, that would be filtered based on input value? Thanks in advance.
Ok, so first thing: You're never going to get this query to fold as it is because you're using a native query as your source i.e. you're using Sql.Database() with an SQL SELECT script.
Based on the simplicity of the SQL query (from what I can see, anyway) it's totally unnecessary to have this query as a native connection to begin with.
Assuming your tables are all coming from the SAME DB, you can set up a foldable query set like this:
Create a new SQL query and connect to [DBName].[TEST] from within the connection dialog. Then multi-select (Ctrl+click) the [Test_ID] and [Place] columns and go to the Home tab > Remove Columns (dropdown) > Remove Other Columns. If you right-click on your 'Remove Other Columns' query step now, you should see 'View Native Query' lit up. Selecting this will show you the native ("folded") query sent to the source.
Rinse and repeat in new queries for [DBName].[RESULT] and any other tables you need to join. For these other tables, you can right-click on the query name and DE-select 'Enable Load'.
Perform the joins between these tables in Power Query and, when you right-click on the query steps in your first query, you should, again, see 'View Native Query' lit up. This will now be sending the full optimised query back to the source for your base table, all the join tables, and your transformations so far. Once you confirm this is working, you can then use my technique(s) to join your SP query in a foldable way.
This is a pretty big topic so I can't go into the Nth degree of detail here, but this is the basic setup of a foldable query set. There's TONS of resources online about Query Folding as a topic, and I've provided a couple of links previously to get you started - please read these as they basically explain what I've described here.
I can tell you this: Once you understand query folding and learn to implement it correctly, it will completely change your Power Query mindset, and have you producing very efficient PBI models.
Pete
Hi dzanka ,
It depends on what your source DB is but, assuming it's SQL Server or similar, one or both of the following should work:
Option 1: Table buffer and INNER join
-1- In a custom step in your DB query, BUFFER the SharePoint table i.e. have Power Query pull it into working memory, like this:
Buffer_SP_Value = Table.Buffer(SP_Query_Name)
-2- Perform and INNER JOIN (inner merge) between your DB_Query column and the SP_Query column.
-3- Click on this new merge/join step and change the SP_Query and column reference to your buffered query step Buffer_SP_Value, something like this:
// Change this:
Table.NestedJoin(
PreviousStepName, {"DB_Query_Column"},
SP_Query, {"SP_Query_Column"},
"NewColumnName",
JoinKind.Inner
)
// To this:
Table.NestedJoin(
PreviousStepName, {"DB_Query_Column"},
Buffer_SP_Value, {"SP_Query_Column"}, // Here's where we change the table reference
"NewColumnName",
JoinKind.Inner
)
Option 2: List buffer and filter
-1- In your DB_Query filter DB_Query_Column by any value (this just sets up the base code for us).
-2- Select this query step then, in the formula bar, update the code something like this:
// Change this:
Table.SelectRows(
PreviousStepName,
each DB_Query[DB_Query_Column] = "XYZ"
)
// To this:
Table.SelectRows(
PreviousStepName,
each List.Contains(
List.Buffer(SP_Query[SP_QueryColumn]),
DB_Query[DB_Query_Column]
)
)
Both of these should work and, assuming they are performed on a foldable source that has maintained folding up to this point, the work should be passed back to the source as a native query.
Try both as you may get performance differences between them.
Pete