locked
Manage Parameter from Database RRS feed

  • Question

  • let
         Source = Xml.Tables(File.Contents("C:\temp\example.trx")),
         #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Results")),
         Table = #"Filtered Rows"{0}[Table],
         #"Expanded Table" = Table.ExpandTableColumn(Table, "Table", {"Attribute:computerName", "Attribute:duration", "Attribute:endTime", "Attribute:executionId", "AttributeSmiley Surprisedutcome", "Attribute:startTime", "Attribute:testFile", "Attribute:testId", "Attribute:testListId", "Attribute:testName", "Attribute:testType"}, {"Table.Attribute:computerName", "Table.Attribute:duration", "Table.Attribute:endTime", "Table.Attribute:executionId", "Table.AttributeSmiley Surprisedutcome", "Table.Attribute:startTime", "Table.Attribute:testFile", "Table.Attribute:testId", "Table.Attribute:testListId", "Table.Attribute:testName", "Table.Attribute:testType"})
     in
         #"Expanded Table" 

    I want to pass below value as variable from SQL database table:

    "C:\temp\example.trx"

    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Thursday, June 9, 2016 6:10 PM
    Thursday, June 9, 2016 6:10 PM

Answers

  • I'm not sure if there is an easier way of doing this, but the steps I normally follow for looping over are the following.

    I start by converting the base query into a function by wrapping it in an outer let / in and add a parameter

    let
        LoadTrx = (filename) =>
    let
        Source = Xml.Tables(File.Contents(filename)),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Results")),
        Table = #"Filtered Rows"{0}[Table],
        #"Expanded Table" = Table.ExpandTableColumn(Table, "Table", {"Attribute:computerName", "Attribute:duration", "Attribute:endTime", "Attribute:executionId", "AttributeSmiley Surprisedutcome", "Attribute:startTime", "Attribute:testFile", "Attribute:testId", "Attribute:testListId", "Attribute:testName", "Attribute:testType"}, {"Table.Attribute:computerName", "Table.Attribute:duration", "Table.Attribute:endTime", "Table.Attribute:executionId", "Table.AttributeSmiley Surprisedutcome", "Table.Attribute:startTime", "Table.Attribute:testFile", "Table.Attribute:testId", "Table.Attribute:testListId", "Table.Attribute:testName", "Table.Attribute:testType"})
    in
        #"Expanded Table"
    in 
        LoadTrx
    

    I saved this query and called it fnLoadTrx

    Then I will make a second query which will run the database query and execute the function for each row in the results. You get back a row of nested tables from the function call so I then expand the results. I've also added the file name in to the final result so that you can tell which file a give row came from.

    let
        files = Sql.Database("localhost\sql12", "tempdb", [Query="select 'c:\temp\example.trx' as filename#(lf)union all select 'c:\temp\example2.trx' as filename"]),
        loop = Table.AddColumn( files, "results", each fnLoadTrx([filename])),
        #"Expanded results" = Table.ExpandTableColumn(loop, "results", {"Name", "Table.Attribute:computerName", "Table.Attribute:duration", "Table.Attribute:endTime", "Table.Attribute:executionId", "Table.AttributeSmiley Surprisedutcome", "Table.Attribute:startTime", "Table.Attribute:testFile", "Table.Attribute:testId", "Table.Attribute:testListId", "Table.Attribute:testName", "Table.Attribute:testType"}, {"results.Name", "results.Table.Attribute:computerName", "results.Table.Attribute:duration", "results.Table.Attribute:endTime", "results.Table.Attribute:executionId", "results.Table.AttributeSmiley Surprisedutcome", "results.Table.Attribute:startTime", "results.Table.Attribute:testFile", "results.Table.Attribute:testId", "results.Table.Attribute:testListId", "results.Table.Attribute:testName", "results.Table.Attribute:testType"})
    in
        #"Expanded results"
    



    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Navind Thursday, June 9, 2016 10:36 PM
    • Unmarked as answer by Navind Thursday, June 9, 2016 10:36 PM
    • Marked as answer by Navind Thursday, June 9, 2016 11:35 PM
    Thursday, June 9, 2016 10:35 PM