Merge two tables with different source using query

[dzanka]

Hi everyone, 

I am trying to merge table from database with the local table, which source is from sharepoint. 

My idea is to reduce time of import data from database, cos I dont need everything. 

I need to import only rows that have matching value in a specific column with the one record from the table from sharepoint (in sharepoint table there is only one column with one value). 

I managed to load all data and then merge queries so I got what I needed, but it takes so much time, because in the db there are a lot of records (import took more than half an hour for reference). Is there a possible way to import from database only rows with matching value? 

I tried to make inner join in source query but my table from sharepoint is obviously not located in database and there is no chance that it ever will be, so I got error because of this. 

Can you please help me to find solution for my problem? Is there any way how can I do this? 

[dzanka · Accepted solution]

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. 

[v-achippa]

Hi @dzanka,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @BA_Pete for addressing the isue.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user for the issue worked? or let us know if you need any further assistance?
If my response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Anjan Kumar Chippa

[dzanka · Accepted solution]

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. 

[BA_Pete]

Hi @dzanka ,

If you're using a foldable source and still having the whole DB loaded prior to filtering, then you've broken query folding. This can either be because you're using a native query connection, but not using the [EnableFolding=true] argument, or you're passing non-foldable transformations to the source prior to applying the filters as I've described.

Have a look at how to enable/maintain folding on your query:

https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding 

https://learn.microsoft.com/en-us/power-query/query-folding-basics 

Once you ensure your query is being passed fully to the source, my filter examples will also get passed to the source.

Pete

[dzanka]

Hi @BA_Pete ,

I'm clearly doing something wrong. 

Here is a simple version of my code, there is join with more tables, there are more columns, but other than that it is pretty much the same.  

let
    IDINPT = Input2{0}, // Input2 is table where is stored value from sharepoint table
sQuery = "

SELECT [T].Test_ID
     , [T].Place
     , [R].Test_Unique_Id
     , [R].Group
     , [R].SubGroup
     , [R].Text
     , [R].Result_Unique_Id
     , [R].TestTime
	 , [R].Result_IO
FROM [DBName].[TEST]      AS [T]
    FULL Join [DBName].[RESULT]        AS [R]
        on [R].[Result_Unique_Id] = [T].Test_Unique_Id
  /* Inner Join Input2 
        on [T].Test_ID = Input2.ID*/
/*Where [T].Test_ID = IDINPT*/
",
    result = Sql.Database(#"ServerName", #"DBName", [Query=sQuery]),
    #"FilteredRows" = Table.NestedJoin(result, {"Test_ID"}, Input2, {"ID"}, "NewColumn", JoinKind.Inner),
    #"Changed Type" = Table.TransformColumnTypes(#"FilteredRows",{{"Group", Int64.Type}, {"Result_Unique_Id", type text}}),
    #"Replaced false for NOT in Result_IO" = Table.ReplaceValue(#"Changed Type","false","NOT",Replacer.ReplaceText,{"Result_IO"}),
    #"Replaced true for OK in Result_IO" = Table.ReplaceValue(#"Replaced false for NOT in Result_IO","true","OK",Replacer.ReplaceText,{"Result_IO"})
in
    #"Replaced true for OK in Result_IO"

 I am editing code in advanced editor and Table name for values from db in PowerBI is ResulTab.  

I also tried these

 //   #"FilteredRows" = Table.SelectRows(result, List.Contains(ResulTab[Test_ID], Input2[ID])),
 //   #"FilteredRows" = Table.SelectRows(result, each List.Contains(List.Buffer(Input2[ID]), ResulTab[Test_ID])),

instead of this

    #"FilteredRows" = Table.NestedJoin(result, {"Test_ID"}, Input2, {"ID"}, "NewColumn", JoinKind.Inner),

 I got this error 

Expression.Error: A cyclic reference was encountered during evaluation. 

 when I use one of these: 

#"FilteredRows" = Table.SelectRows(result, List.Contains(ResulTab[Test_ID], Input2[ID])),
#"FilteredRows" = Table.SelectRows(result, each List.Contains(List.Buffer(Input2[ID]), ResulTab[Test_ID])),

 And with this version 

#"FilteredRows" = Table.NestedJoin(result, {"Test_ID"}, Input2, {"ID"}, "NewColumn", JoinKind.Inner),

 it just took a lot of time and sometimes I got the message out of memory.

 Here is the code for table from sharepoint, table name is Input2.

let
    Source = SharePoint.Files("https://...sharepoint.com/sites/Statistic/", [ApiVersion = 15]),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"Column1", "KNR"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Source.Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", type text}})
in
    #"Changed Type"

And I am working in PowerBI desktop. 

Is there something that I am missing?

PS: I'm still waiting for the light bulb to go on in my head.  😅

[BA_Pete · Accepted solution]

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

[dzanka]

Hi @BA_Pete ,

Firstly, many thanks for your patience and helpful answers. I did a small research, tried to read every possible source I could find, but I still have a problem. 

I've changed the connection to data. I have also merged data from all tables I needed and still managed to see View Native Query. 

For simplicity and time saving I was then working on just one table without merging the others.

On this one table I tried both ways you have recommended - inner join and filter. 

Both of them did the job but here is the thing - after I use one of these, it's not folding anymore and I can't see Native Query.

When I tried to filter by specific value it was foldable till I use value from sharepoint table. 

I am not sure, if I'm still doing sth wrong or it's just not possible to do it foldable way. 

Query1 is Sharepoint table query. 

These are codes I have tried: 

let
    Source = Sql.Database(ServerName, DBName),
    U_Test = Source{[Schema="U", Item="Test"]}[Data], 
    #"Removed Other Columns1" = Table.SelectColumns(U_Test, {Test_Id, Test_Nr, Test_Place}), 
    InptNR = Table.Buffer(Query1), 
    #"Merged Queries1" = Table.NestedJoin(#"Removed Other Columns1", {Test_NR, InptNR, {NR}, "Query1", JoinKind.Inner)

in
    #"Merged Queries1"

And:

let
    Source = Sql.Database(ServerName, DBName),
    U_Test = Source{[Schema="U", Item="Test"]}[Data], 
    #"Removed Other Columns1" = Table.SelectColumns(U_Test, {Test_Id, Test_Nr, Test_Place}), 
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each List.Contains (List.Buffer(Query1[NR], [Test_KNR]))

in
    #"Filtered Rows"

Step "Removed Other Columns1" is still foldable, the next  one isn't. I tried to figure it out on my own, but I just don't get what happend in between these steps and what is the problem. 

Many thanks for any advice. 

[BA_Pete]

On the second one, it looks like you're missing a closing bracket here:

No sure how the query ran with this error, but try correcting and see how you get on.

Pete

[dzanka]

Hi @BA_Pete , 

I've checked and I have the brackets correctly in my program, it just dissapeared during the copy process. 

[BA_Pete]

Ok. Try adding another foldable step after the filter step (just for testing) e.g. Remove Other Columns or something. Sometimes Power Query doesn't 'know' it can fold the query at certain query steps. You might find that, by adding a basic foldable step after this one, your final query step does actually fold, including the filter step.

You may also decide that the overall query now runs fast enough with everything else folding and just this one step not folding at the end of the query - that's an acceptable outcome too.

Pete

[dzanka]

I've added a new step, where I have removed one column, but it is still not folding. Well it take too much time to refresh because of size of data it has to import. Maybe this is simply not possible to perform in Power BI effectively, I'll try different program. Thank you so much for helpful advices, I will definitely use them in different projects. 

[BA_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