none
How do I utilize table column or range as a variable in SQL Query RRS feed

  • Question

  • Hi everyone,

    Edited for simplicity. 

    I am working on Power Query and I have a question about using a parameter for the query of PowerQuery.

    I would like to know how <g class="gr_ gr_75 gr-alert gr_tiny gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling multiReplace" data-gr-id="75" id="75">i</g> could use a table column in a worksheet as a variable into a request for Oracle <g class="gr_ gr_105 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="105" id="105">sql</g>?

    Instead of copying and pasting the formatted numbers into my query id like to reference Table1 

    something like the below where statement with Table1 equaling a column in my table. The column contains a few hundred rows of formatted text I currently past directly into the SQL. for example

    '000000000000000000', 
    '000000000000000001', 
    '000000000000000003', 

    I tried steps I found for utilizing a single cell but can't make it work with either the column or a single cell. I receive DataSource.Error: Oracle: ORA-00904: "TABLE1": invalid identifier.

    WHERE

    ORACLE_TABLE.ACC_NUM IN ('000000000000000000', Table1 
    '000000000000000000')

    Thanks

    The Steps I followed are in this thread. 

    https://social.technet.microsoft.com/Forums/en-US/95dd4361-cffb-4b12-820a-85c3d041db3e/power-query-<g class="gr_ gr_1229 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1229" id="1229">query</g>-with-<g class="gr_ gr_1230 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1230" id="1230">parameter</g>-from-a-cell?forum=powerquery


    • Edited by Matt_KP Thursday, September 27, 2018 5:16 PM
    Thursday, September 27, 2018 4:42 PM

Answers

  • Try pulling out the value like this (note the bold text):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACC FORMATTED", type text}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ACC FORMATTED"}),
        #"ACC FORMATTED" = #"Removed Other Columns"[ACC FORMATTED]{0}
    in
        #"ACC FORMATTED"

    Or, you could simplify and use the following M expression:

    Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ACC FORMATTED]{0}

    Ehren

    Monday, October 8, 2018 6:03 PM
    Owner

All replies

  • Hi Matt. Can you share the M formulas for that produce the DataSource.Error you shared above?

    Ehren

    Monday, October 1, 2018 7:33 PM
    Owner
  • Sorry I never got the notice. I'm not sure what an M formula is? I'm rather new to this frankly

    The table containing the data I hope to utilize has this... I'd prefer to pull it directly from the source and not create an additional table but thought the query was required to utilize it as a source.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACC FORMATTED", type text}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ACC FORMATTED"}),
        #"ACC FORMATTED" = #"Removed Other Columns"[ACC FORMATTED]
    in
        #"ACC FORMATTED"

    Monday, October 8, 2018 5:59 PM
  • Try pulling out the value like this (note the bold text):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACC FORMATTED", type text}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ACC FORMATTED"}),
        #"ACC FORMATTED" = #"Removed Other Columns"[ACC FORMATTED]{0}
    in
        #"ACC FORMATTED"

    Or, you could simplify and use the following M expression:

    Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ACC FORMATTED]{0}

    Ehren

    Monday, October 8, 2018 6:03 PM
    Owner