Forum Discussion

jnickell's avatar
jnickell
Helper V
6 years ago

Using a variable for statement in PowerQuery Beta for SSIS

I'm trying to use the PowerQuery Beta for some SSIS packages.  I was curious if any one had used this, in particular the ability to set a PowerQuery equivalent to a variable.  I've "gotten" it to wor...
Need Help
jnickell's avatar
jnickell
Helper V
2 years ago

I wound up coming back to this for a new project and believe I figured out what I needed to do to translate Power Query in Power BI to a SSIS variable.  Mainly documenting this for my future self.
The main thing is that any " characters need to be escaped and if you're using a variable inline, the string needs to be broken and concatenated with the variable using a + .  

 

Hope this is helpful to others. 

Original PQ sample

let
    Source = Folder.Files("C:\Users\me\Completed\"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xml")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
in
    #"Renamed Columns1"

 

I used used Find & Replace in a text editor and then tweaked to include the variable I needed

"
let
    Source = Folder.Files(\"" + @[User::MyVariable] + "\"),
    #\"Filtered Rows\" = Table.SelectRows(Source, each ([Extension] = \".xml\")),
    #\"Filtered Hidden Files1\" = Table.SelectRows(#\"Filtered Rows\", each [Attributes]?[Hidden]? <> true),
    #\"Invoke Custom Function1\" = Table.AddColumn(#\"Filtered Hidden Files1\", \"Transform File\", each #\"Transform File\"([Content])),
    #\"Renamed Columns1\" = Table.RenameColumns(#\"Invoke Custom Function1\", {\"Name\", \"Source.Name\"}),
in
    #\"Renamed Columns1\"
"