locked
Best practice to check (table) contents of web page RRS feed

  • Question

  • Below is an example of a query that is used repeatedly.  The bold items signify the parameter variables.  Under normal circumstances there are 2 (data) tables as the code suggests.  But, this call returns no tables and other calls may return just one table.  So it throws a run-time error.  How best to deal with this via some conditional logic?  Check enumerator value, how? Many thanks.

    Example: Returns no tables

    let
        Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s=SPY&m=2016-06")),

        Data1 = Source{1}[Data],
        Data2 = Source{2}[Data],
        Data3 = Table.Combine({Data1, Data2}),
        #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Strike", type number}, {"Symbol", type text}, {"Last", type number}, {"Chg", type number}, {"Bid", type text}, {"Ask", type text}, {"Vol", Int64.Type}, {"Open Int", Int64.Type}})
    in
        #"Changed Type"

    Example: Returns two tables

    let
        Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s=SPY&m=2016-08")),
          Data1 = Source{1}[Data],
        Data2 = Source{2}[Data],
        Data3 = Table.Combine({Data1, Data2}),
        #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Strike", type number}, {"Symbol", type text}, {"Last", type number}, {"Chg", type number}, {"Bid", type text}, {"Ask", type text}, {"Vol", Int64.Type}, {"Open Int", Int64.Type}})
    in
        #"Changed Type"

    Friday, July 15, 2016 9:55 AM

Answers

  • I would go a different path and create one query where all the results are returned. You can then filter what you need. I've also changed the steps to avoid invalid calls. Please let me know if you need any explanation here:

    let
    function = (Period) =>
    let
         Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s=SPY&m="&Period)),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Id] = null) and ([Caption] = null)),
         Data3 = Table.Combine({#"Filtered Rows", #"Filtered Rows"}),
        #"Removed Other Columns" = Table.SelectColumns(Data3,{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}),
         #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Strike", type number}, {"Symbol", type text}, {"Last", type number}, {"Chg", type number}, {"Bid", type text}, {"Ask", type text}, {"Vol", Int64.Type}, {"Open Int", Int64.Type}})
    in #"Changed Type",
    
        table = #table({"Period"}, {{"2016-06"}, {"2016-07"}}),
        CallFunction = Table.AddColumn(table, "Custom", each function([Period])),
        ExpandFunctionResults = Table.ExpandTableColumn(CallFunction, "Custom", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}),
        Filter = Table.SelectRows(ExpandFunctionResults, each ([Ask] <> null))
     in
         Filter


    Imke Feldmann TheBIccountant.com

    Sunday, July 17, 2016 10:20 AM
  • I've modified Rui's script a bit in order to meet my needs (https://ruiromanoblog.wordpress.com/2016/01/08/power-bi-desktop-trace-logs-analyser/) - this might help you as well: Added some filters and expanded the fields returned (so now you see the URLs as well) - definitely sth that can be played around even further :-)

    let
        Source = Folder.Files(#"VAR_LogFolder"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".log")),
        #"Removed ColumnsA" = Table.RemoveColumns(#"Filtered Rows",{"Extension", "Date accessed", "Date created", "Attributes", "Folder Path"}),
        #"Renamed ColumnsP" = Table.RenameColumns(#"Removed ColumnsA",{{"Name", "Log File"}, {"Date modified", "Log File Date"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed ColumnsP", "LogText", each Lines.FromBinary([Content])),
        #"Removed ColumnsX" = Table.RemoveColumns(#"Added Custom",{"Content"}),
        #"Expanded LogText" = Table.ExpandListColumn(#"Removed ColumnsX", "LogText"),
    //Added Filter  
        #"Filtered Rows3" = Table.SelectRows(#"Expanded LogText", each (Text.Contains([LogText], "Url") or Text.Contains([LogText], "Engine/User") or Text.Contains([LogText], "SQL")) and not Text.Contains([LogText], "ClientConfigStorage")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows3","LogText",Splitter.SplitTextByEachDelimiter({" : "}, QuoteStyle.None, false),{"Column1.1", "Column1.2"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter","Column1.1",Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false),{"Column1.1.1", "Column1.1.2"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1","Column1.1.1",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false),{"Column1.1.1.1", "Column1.1.1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.1.1", type text}, {"Column1.1.1.2", type text}, {"Column1.1.2", Int64.Type}, {"Column1.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1.1.1.1"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Column1.1.1.2",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Column1.1.1.2.1", "Column1.1.1.2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.1.1.2.1", type text}, {"Column1.1.1.2.2", type text}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Column1.1.1.2.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.1.1.2.2", "Level"}, {"Column1.1.2", "Trace Id"}}),
        #"Parsed JSON" = Table.TransformColumns(#"Renamed Columns",{{"Column1.2", Json.Document}}),
    //    ListOfFields = Record.FieldNames(Record.Combine(#"Parsed JSON"[Column1.2])),
    //Expand more columns
        #"Expanded Column1.2" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1.2", {"Start", "Action", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "evaluationID", "URL", "RequestURL"}, {"Start", "Action", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "evaluationID", "URL", "RequestURL"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Column1.2",{{"Start", type datetime}, {"Duration", type duration}}),
        #"Calculated Total Seconds" = Table.TransformColumns(#"Changed Type2",{{"Duration", Duration.TotalSeconds}}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Calculated Total Seconds",{{"evaluationID", Int64.Type}, {"ResponseFieldCount", Int64.Type}, {"Tid", Int64.Type}, {"Pid", Int64.Type}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type3", "Index", 1, 1),
        #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Log Id"}, {"evaluationID", "EvaluationId"}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Action", "Action - Copy"),
        #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"Action - Copy", "Action Detail"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Log File", "Log File Date", "Level", "Trace Id", "Start", "Action", "Action Detail", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "EvaluationId", "Log Id"}),
        #"Split Column by Delimiter4" = Table.SplitColumn(#"Reordered Columns","Action",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Action.1", "Action.2", "Action.3", "Action.4", "Action.5", "Action.6"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Action.1", type text}, {"Action.2", type text}, {"Action.3", type text}, {"Action.4", type text}, {"Action.5", type text}, {"Action.6", type text}}),
        #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type4", "Start", "Start - Copy"),
        #"Changed Type5" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Start - Copy", type time}}),
        #"Renamed Columns3" = Table.RenameColumns(#"Changed Type5",{{"Start - Copy", "Hour"}, {"Start", "Date"}}),
        #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Date", type date}}),
        #"Removed Errors" = Table.Buffer(Table.RemoveRowsWithErrors(#"Changed Type6", {"Log File"})),
        #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Log File", "Log File Date", "Level", "Trace Id", "Date", "Action.1"}),
        #"Filtered Rows1" = Table.SelectRows(#"Removed Errors1", each ([Level] <> null and [Level] <> ""))
    in
        #"Filtered Rows1"

    I found it best to turn off tracing before running the script that analyses the trace results.

    With regards to Web calls: It looks as if the calls are made immediately after each other, so no waiting for the results to be returned.


    Imke Feldmann TheBIccountant.com


    Tuesday, August 16, 2016 6:40 AM

All replies

  • I would go a different path and create one query where all the results are returned. You can then filter what you need. I've also changed the steps to avoid invalid calls. Please let me know if you need any explanation here:

    let
    function = (Period) =>
    let
         Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s=SPY&m="&Period)),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Id] = null) and ([Caption] = null)),
         Data3 = Table.Combine({#"Filtered Rows", #"Filtered Rows"}),
        #"Removed Other Columns" = Table.SelectColumns(Data3,{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}),
         #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Strike", type number}, {"Symbol", type text}, {"Last", type number}, {"Chg", type number}, {"Bid", type text}, {"Ask", type text}, {"Vol", Int64.Type}, {"Open Int", Int64.Type}})
    in #"Changed Type",
    
        table = #table({"Period"}, {{"2016-06"}, {"2016-07"}}),
        CallFunction = Table.AddColumn(table, "Custom", each function([Period])),
        ExpandFunctionResults = Table.ExpandTableColumn(CallFunction, "Custom", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}),
        Filter = Table.SelectRows(ExpandFunctionResults, each ([Ask] <> null))
     in
         Filter


    Imke Feldmann TheBIccountant.com

    Sunday, July 17, 2016 10:20 AM
  • Hi Imke,

    Thank you for a simple but elegant solution.  My main takeaway is the filter statement:

        #"Filtered Rows" = Table.SelectRows(Source, each ([Id] = null) and ([Caption] = null)),

    since it achieves the original requirement to only return valid data tables.  Can the conditional logic be considered as a general rule of thumb or is it just applicable to this situation based on your observations of the web queries?  Also, why this statement:

         Data3 = Table.Combine({#"Filtered Rows", #"Filtered Rows"}),

    since it duplicates the data.

    The PROD query requires appx. 23k web queries (symbol/period combos) resulting in upwards of 1MM rows so, yes, custom column/function is way to go.  I'd like to share the final code for your comments so I'll leave this open for now.  Many thanks again

    Sunday, July 17, 2016 12:37 PM
  • Yes, Data3 does what you say and should be deleted.

    If errors are returned, one can use try ... otherwise. Else one could write conditions like "if Table.RowCount(result) > 0 then result else AnotherStep". But this can be a lit tricky to integrate into the existing query.

    So in your example it was just by observation to see which pattern the first table had and calling this all from an existing table makes writing the conditions obsolete.


    Imke Feldmann TheBIccountant.com


    Sunday, July 17, 2016 3:01 PM
  • Hi Imke,

    The web site appears to implement a throttle or I am not doing something correctly.  I get "Waiting...." messages and the query is too slow.  I've implemented a "wait" between web queries.  Perhaps you can take a quick look and let me know if this was implemented correctly.  Many thanks.

    let
        Source = Csv.Document(File.Contents("C:\Users\NJInv\Documents\Business Intelligence\Projects\Market Data\Data\OPRA.txt"),null,"#(tab)",null,1252),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers","Description",Splitter.SplitTextByDelimiter(" "),{"Description.1", "Description.2", "Description.3", "Description.4"}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "Description.2", "Description.2 - Copy"),
        #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Description.2 - Copy", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each [#"Description.2 - Copy"] >= #date(2016, 6, 3)),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows","Description.2",Splitter.SplitTextByDelimiter("-"),{"Description.2.1", "Description.2.2", "Description.2.3"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Description.1", type text}, {"Description.2.1", Int64.Type}, {"Description.2.2", type text}, {"Description.2.3", Int64.Type}, {"Description.3", type number}, {"Description.4", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","Jan","01",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Feb","02",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Mar","03",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Apr","04",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","May","05",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Jun","06",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Jul","07",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Aug","08",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Sep","09",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Oct","10",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Nov","11",Replacer.ReplaceText,{"Description.2.2"}),
        #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Dec","12",Replacer.ReplaceText,{"Description.2.2"}),
        #"Removed Duplicates" = Table.Distinct(#"Replaced Value11", {"Description.1", "Description.2.2", "Description.2.3"}),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Symbol"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Description.1", "Symbol"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Description.2.1", "Description.3", "Description.4", "Description.2 - Copy"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Description.2.3", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each [Description.2.3]&"-"&[Description.2.2]),
        #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "ExpiryMonth"}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Description.2.2", "Description.2.3"}),
        #"Added Custom2" = Table.AddColumn(#"Removed Columns2", "Custom", each let
        GetTimeAsText = ()=> DateTime.ToText(DateTime.LocalNow()),
        Output = Function.InvokeAfter(GetTimeAsText, #duration(0,0,0,1)),
    
         Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s="&[Symbol]&"&m="&[ExpiryMonth])),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Id] = null) and ([Caption] = null)),
         #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"})
    in
        #"Expanded Data"),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Custom.Ask", "Custom.Bid", "Custom.Chg", "Custom.Last", "Custom.Open Int", "Custom.Strike", "Custom.Symbol", "Custom.Vol"})
    in
        #"Expanded Custom"

    Friday, July 22, 2016 10:16 AM
  • Hi Nin,

    I have to experience with Function.Invoke after and cannot spot anything obviously wrong in your query. In order to get hold of the performance-problem I'd suggest the following:

    - split up your query into the the generation of the table from which you're calling the function/creating the column and load that first table to Excel. There you reduce the number of rows to a handful and load that table back to Power Query. That way you can slowly increase the number of calls and check out when the timing gets serious and try to tackle it.

    - don't use the wait-command. I cannot see what you're going to win - and this will of course cause waiting time in your query.

    - wrap the step before you're calling the function (currently #"Removed Custom2") into a Table.Buffer(....) That way it should prevent the previous calls being made multiple times.


    Imke Feldmann TheBIccountant.com

    Friday, July 22, 2016 3:56 PM
  • Thanks Imke,

    I'll make changes per your suggestions.  This has me very interested...

    - wrap the step before you're calling the function (currently #"Removed Custom2") into a Table.Buffer(....) That way it should prevent the previous calls being made multiple times

    Why would previous call be made multiple times?

    Many thanks again..

    Saturday, July 23, 2016 9:44 AM
  • The short explanation is: Sometimes this helps - and it doesn't hurt if applied to small tables.

    The longer one: Power Query is designed to cache results of queries that have been made once and re-use these results whereever else they are needed. This way preventing same calls being made multiple times. So in your case that would be for every function call in every row of your table (that has been generated with multiple steps).

    But sometimes this doesn't happen. I haven't found a documentation about this so I cannot really tell when this happens. But you will definitely experience it with functions like List.Generate. It is almost impossible to end up with reasonable query times without using buffers there.


    Imke Feldmann TheBIccountant.com

    Sunday, July 24, 2016 5:26 AM
  • Thank you Imke for enlightening me to this PQ caveat with tables (and lists).  Is this implementation correct usage of Table.Buffer as you suggest?!

    let
        	Source = Excel.CurrentWorkbook(){[Name="YAHOOExpiryMonthTable"]}[Content],
      	#"Removed Bottom Rows" = Table.RemoveLastN(Source,21120),
    	BufferedInputTable = Table.Buffer(#"Removed Bottom Rows"),
        #"Added Custom" = Table.AddColumn(BufferedInputTable, "Custom", each let
        Source = Web.Page(Web.Contents("http://finance.yahoo.com/q/op?s="&[Symbol]&"&m="&[ExpiryMonth])),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Id] = null) and ([Caption] = null)),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"})
    in
        #"Expanded Data"),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ask", "Bid", "Chg", "Last", "Open Int", "Strike", "Symbol", "Vol"}, {"Custom.Ask", "Custom.Bid", "Custom.Chg", "Custom.Last", "Custom.Open Int", "Custom.Strike", "Custom.Symbol", "Custom.Vol"})
    in
        #"Expanded Custom"

    In this example I filter down to about 100 rows.  The query immediate displays it is waiting for site.  It's possible the query is causing the site to implement some QOS function. Is the order of execution row by row meaning does the query make the url call and wait for the results before executing the next (url call) row?  Or does the query issue all of the calls at once?  Many thanks again.

    

    Sunday, July 24, 2016 1:16 PM
  • Sorry for the late answer, somehow missed your reply here.

    Yes, this is how the Buffer should be applied.

    Cannot tell how the web-calls are executed, but you can try using the trace-file order to find it out. Check https://blog.crossjoin.co.uk/2016/06/09/power-bi-diagnostics-trace-logs-and-query-execution-times-again/ for example.


    Imke Feldmann TheBIccountant.com

    Monday, August 15, 2016 6:44 AM
  • Thanks for your help, Imke.  I'll check out Chris' blog post and report back.  I'll leave this open for now.
    Monday, August 15, 2016 7:52 PM
  • I've modified Rui's script a bit in order to meet my needs (https://ruiromanoblog.wordpress.com/2016/01/08/power-bi-desktop-trace-logs-analyser/) - this might help you as well: Added some filters and expanded the fields returned (so now you see the URLs as well) - definitely sth that can be played around even further :-)

    let
        Source = Folder.Files(#"VAR_LogFolder"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".log")),
        #"Removed ColumnsA" = Table.RemoveColumns(#"Filtered Rows",{"Extension", "Date accessed", "Date created", "Attributes", "Folder Path"}),
        #"Renamed ColumnsP" = Table.RenameColumns(#"Removed ColumnsA",{{"Name", "Log File"}, {"Date modified", "Log File Date"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed ColumnsP", "LogText", each Lines.FromBinary([Content])),
        #"Removed ColumnsX" = Table.RemoveColumns(#"Added Custom",{"Content"}),
        #"Expanded LogText" = Table.ExpandListColumn(#"Removed ColumnsX", "LogText"),
    //Added Filter  
        #"Filtered Rows3" = Table.SelectRows(#"Expanded LogText", each (Text.Contains([LogText], "Url") or Text.Contains([LogText], "Engine/User") or Text.Contains([LogText], "SQL")) and not Text.Contains([LogText], "ClientConfigStorage")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows3","LogText",Splitter.SplitTextByEachDelimiter({" : "}, QuoteStyle.None, false),{"Column1.1", "Column1.2"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter","Column1.1",Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false),{"Column1.1.1", "Column1.1.2"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1","Column1.1.1",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false),{"Column1.1.1.1", "Column1.1.1.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Column1.1.1.1", type text}, {"Column1.1.1.2", type text}, {"Column1.1.2", Int64.Type}, {"Column1.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1.1.1.1"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns","Column1.1.1.2",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Column1.1.1.2.1", "Column1.1.1.2.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Column1.1.1.2.1", type text}, {"Column1.1.1.2.2", type text}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Column1.1.1.2.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.1.1.2.2", "Level"}, {"Column1.1.2", "Trace Id"}}),
        #"Parsed JSON" = Table.TransformColumns(#"Renamed Columns",{{"Column1.2", Json.Document}}),
    //    ListOfFields = Record.FieldNames(Record.Combine(#"Parsed JSON"[Column1.2])),
    //Expand more columns
        #"Expanded Column1.2" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1.2", {"Start", "Action", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "evaluationID", "URL", "RequestURL"}, {"Start", "Action", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "evaluationID", "URL", "RequestURL"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Column1.2",{{"Start", type datetime}, {"Duration", type duration}}),
        #"Calculated Total Seconds" = Table.TransformColumns(#"Changed Type2",{{"Duration", Duration.TotalSeconds}}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Calculated Total Seconds",{{"evaluationID", Int64.Type}, {"ResponseFieldCount", Int64.Type}, {"Tid", Int64.Type}, {"Pid", Int64.Type}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type3", "Index", 1, 1),
        #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Log Id"}, {"evaluationID", "EvaluationId"}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Action", "Action - Copy"),
        #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"Action - Copy", "Action Detail"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Log File", "Log File Date", "Level", "Trace Id", "Start", "Action", "Action Detail", "ProductVersion", "ActivityId", "Process", "Pid", "Tid", "Duration", "DbProviderName", "Exception", "CommandText", "ResponseFieldCount", "EvaluationId", "Log Id"}),
        #"Split Column by Delimiter4" = Table.SplitColumn(#"Reordered Columns","Action",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"Action.1", "Action.2", "Action.3", "Action.4", "Action.5", "Action.6"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Action.1", type text}, {"Action.2", type text}, {"Action.3", type text}, {"Action.4", type text}, {"Action.5", type text}, {"Action.6", type text}}),
        #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type4", "Start", "Start - Copy"),
        #"Changed Type5" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Start - Copy", type time}}),
        #"Renamed Columns3" = Table.RenameColumns(#"Changed Type5",{{"Start - Copy", "Hour"}, {"Start", "Date"}}),
        #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Date", type date}}),
        #"Removed Errors" = Table.Buffer(Table.RemoveRowsWithErrors(#"Changed Type6", {"Log File"})),
        #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Log File", "Log File Date", "Level", "Trace Id", "Date", "Action.1"}),
        #"Filtered Rows1" = Table.SelectRows(#"Removed Errors1", each ([Level] <> null and [Level] <> ""))
    in
        #"Filtered Rows1"

    I found it best to turn off tracing before running the script that analyses the trace results.

    With regards to Web calls: It looks as if the calls are made immediately after each other, so no waiting for the results to be returned.


    Imke Feldmann TheBIccountant.com


    Tuesday, August 16, 2016 6:40 AM