Forum Discussion
Resolver IFiltering/Joining/Merging A Large DB table against an XLS file
Hi here is a turn key solution ( excuse the pun) example joining the backend to an XLS, This will avoid the default action of pulling the whole table down. It uses SAP hana but i'm sure it could be used for other dbs. The key column in this example is a text field so needs single quotes here and there. Assume 'query' is an item that you can connect to normally in power bi. Enjoy
let Source2 = Excel.Workbook(File.Contents("\\yourpath\KeyList.xlsx"), null, true), Sheet1_Sheet = Source2{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers1" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), Source1 = #"Promoted Headers1", KeyList = Text.Combine(Source1[KEYfield],"','") , // column to single line of text sel2 = "select * from ""ZXXX1.ZXXX_REP.bp::Query"" where ""KeyField"" IN ('" & KeyList & "') ", Source = SapHana.Database("server:portnumber", [Query=sel2]) in Source
Resolver IHi
Ok
1) Create a query of the EXCEL spreadsheet with just one column the KEY column only
call this query KEYLISTQUERY and remove duplicates.
2) Then create another query. in the adavance query editor paste this.
let
#"Changed Type" = Table.TransformColumnTypes(#"KEYLISTQUERY",{{"KEY", type text}}),
Custom1 = "'" & Text.Combine(#"Changed Type"[KEY],"','") & "'"
in
Custom1
The output of this is something like this
'key1','key2','key3' - (This video here helped me create comma separated list.)
We are going to use this text in the dynamic where clause later. This assumes you have a text column number columns and dates will be different, of course.
3. Right mouse on the this new query and select create a function called KEYListfunction
with no parameters. to run this function press the invoke button after clicking on the function. This function output the query 'key1','key2','key3'.
4. Finally create a another query . This now has the dynamic where clause. Which will push down the query to the server. This is what we want to do the join to the XLS in the hana server not in power bi pc client.
let
xx = KEYListfunction(),
sel = "select * from ""ZXXX.XXX::mytable"" where ""KEYCOL"" IN (" & xx & ") ",
Source = SapHana.Database("myhanaserver:12345", [Query=sel])
in
SourceFinally is you get a weird Firewall error see this post here
Have tested with a XLS key list of 12000 against a HANA TABLE 9 million row. Once you have the data its a lot quicker as you don't download the 9 million!
Resolver IHi here is a turn key solution ( excuse the pun) example joining the backend to an XLS, This will avoid the default action of pulling the whole table down. It uses SAP hana but i'm sure it could be used for other dbs. The key column in this example is a text field so needs single quotes here and there. Assume 'query' is an item that you can connect to normally in power bi. Enjoy
let
Source2 = Excel.Workbook(File.Contents("\\yourpath\KeyList.xlsx"), null, true),
Sheet1_Sheet = Source2{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
Source1 = #"Promoted Headers1",
KeyList = Text.Combine(Source1[KEYfield],"','") , // column to single line of text
sel2 = "select * from ""ZXXX1.ZXXX_REP.bp::Query"" where ""KeyField"" IN ('" & KeyList & "') ",
Source = SapHana.Database("server:portnumber", [Query=sel2])
in
SourceI know this is a very old post but hoping someone has an answer. Has anyone been able to get this to work once published to the Power BI service and refreshing through a gateway? I seem to get the privacy errors no matter what I set the levels to in the gateway data sources.
