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 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. 😅
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 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.