Search code examples
sqlhadooppowerbipowerqueryimpala

Filter of data while importing from SQL in PowerBI

I need some help with this: I have a huge data set in Hadoop which I have connected with PowerBI.

I also have another table in Excel which contains list of customers I need to filter and take out details from the main table in SQL.

I converted the table in list and made a parameter named "kadam", and used this query:

let
Source = #!"Odbc.Query(""dsn=impala"", ""SELECT * FROM analytics_n_reporting.v_lpm_smth_liab_consld_acct_details WHERE cod_cust_id IN (""kadam""))"
in
Source

I get this error:

Expression.SyntaxError: Token ',' expected.

Please help here, ultimately I need to use the table from Excel to filter data while importing from Hadoop; I don't want to use merge as it will take a lot of time and not using relationship either as I need to do further conditional analysis in Power Query.

Solution

  • You will need ampersands to join the strings:

    let
Source = Odbc.Query("dsn=impala", "SELECT * FROM analytics_n_reporting.v_lpm_smth_liab_consld_acct_details WHERE cod_cust_id IN (" & kadam & ")")
in
Source

    kadam will also need to be a comma separated list of values, it can't just reference a list. To convert your list to a comma-separated string then try:

    Combiner.CombineTextByDelimiter(",")(<<your list or table column>>)