Forum Discussion

ozmike's avatar
ozmike
Resolver I
9 years ago
Solved

Filtering/Joining/Merging A Large DB table against an XLS file

Hi    I have a table with 9 million rows in a readonly database. I get supplied a list of record say 12000 keys in an XLS which I want to filter against. I can't use direct query as I'm joining XLS...
Need Help
  • ozmike's avatar
    ozmike
    9 years ago

    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
ozmike's avatar
ozmike
Resolver I
9 years ago

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
cphilpotts's avatar
cphilpotts
Frequent Visitor
6 years ago

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