Improve refresh time by batching Web API calls – Part 2

Following my last post here, Imke Feldmann, author of THE BICCOUNTANT, proposed a refined version for the solution. While my original solution is simpler for users who are reluctant of manipulating Power Query (M) expressions, Imke’s solution is simpler for advanced users. It contains less steps (It’s shorter by 8 UI step, and 10 transformation steps in M) and it’s more robust.

Here is the original expression we created through the Query Editor in Part 1:

let
    Source = Excel.CurrentWorkbook(){[Name="Symbols"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Symbol", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Index", "Index - Copy"),
    #"Calculated Modulo" = Table.TransformColumns(#"Duplicated Column", {{"Index", each Number.Mod(_, 100), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Calculated Modulo",{{"Index", "Mod"}}),
    #"Integer-Divided Column" = Table.TransformColumns(#"Renamed Columns", {{"Index - Copy", each Number.IntegerDivide(_, 100), Int64.Type}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Index - Copy", "Integer-Divide"}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns3", {{"Mod", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns3", {{"Mod", type text}}, "en-US")[Mod]), "Mod", "Symbol"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Divide"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99"},Combiner.CombineTextByDelimiter("+", QuoteStyle.None),"Symbols"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns","Symbols",Splitter.SplitTextByEachDelimiter({"++"}, QuoteStyle.Csv, false),{"Symbols.1", "Symbols.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Symbols.1", type text}, {"Symbols.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Symbols.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Symbols.1", "Symbols"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns1", "FnGetYahooData", each FnGetYahooData([Symbols])),
    #"Expanded FnGetYahooData" = Table.ExpandTableColumn(#"Invoked Custom Function", "FnGetYahooData", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded FnGetYahooData",{"Symbols"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column1", "Symbol"}, {"Column2", "Name"}, {"Column3", "Ask"}, {"Column4", "Bid"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","N/A","",Replacer.ReplaceText,{"Ask"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N/A","",Replacer.ReplaceText,{"Bid"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Ask", type number}, {"Bid", type number}})
in
    #"Changed Type2"

While that solution was simple to follow and was 100% driven by the user interface (didn’t require any tweaks on M), it has a major disadvantage. If you will run it on a table of less than 100 rows, the query will break.

Let’s demonstrate our edge case:

Running the query on 80 symbols (In my case I just added a third step and kept the first 80 rows by clicking Home –> Keep Rows –> Keep Top Rows) will fail with an error.

In the highlighted step below, you can see that we have a single row following the Pivot step, with columns 0,1,2,…79.

Then in the next step, Power Query will fail to merge the columns:

The reason for the failure is that the Query Editor created for us the following line in the M expression:

#"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99"},Combiner.CombineTextByDelimiter("+", QuoteStyle.None),"Symbols"),

As you can see, all the column names are hard-coded in the expression above. Power Query expects to merge 100 columns, but columns 80, 81, 82, … , 99 are missing, so the transformation step fails.

While the immediate solution to fix this step is to rewrite it, as I recommended here, Imke’s approach is even better.

Instead of the pivoting, and the merging of columns that we used in Part 1, we will group the rows into a sub-tables of 100 rows each, convert each of the 100 symbols in the sub-tables into a list, and apply Text.Combine to convert the list into text.

Before I show you the complete solution step-by-step, here is the improved version.

let
    Source = Excel.CurrentWorkbook(){[Name="Symbols"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Symbol", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 100), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Symbols", each Text.Combine(_[Symbol],"+"), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "FnGetYahooData", each FnGetYahooData([Symbols])),
    #"Expanded FnGetYahooData" = Table.ExpandTableColumn(#"Invoked Custom Function", "FnGetYahooData", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded FnGetYahooData",{"Symbols"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column1", "Symbol"}, {"Column2", "Name"}, {"Column3", "Ask"}, {"Column4", "Bid"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","N/A","",Replacer.ReplaceText,{"Ask"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N/A","",Replacer.ReplaceText,{"Bid"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Ask", type number}, {"Bid", type number}})
in
    #"Changed Type2"

For those of you who wish to implement the solution step by step, here is the walkthrough:

The Walkthrough

Download this Excel workbook to start the tutorial.

Select any cell in table Symbols and click From Table in the section Get & Transform of Data ribbon (Note: if you are not on Excel 2016, you will find From Table in Power Query ribbon after you install the add-in. You can also follow this tutorial with Power BI Desktop).

In Create Table select My table has headers and click OK.

In the Query Editor window, select the column Symbol and click Add Index Column in Add Column tab.

Select the column Index, and in Transform tab, select Standard and click Integer-Divide.

In Integer-Divide set 100 as Value, and click OK.

Select column Index, and click Group By.

Set Symbols as New column name and select All Rows as Operation. When you are done click OK.

Following the last step we now have sub-tables of 100 symbols on each row (as shown in the screenshot below). Now it’s the time to tweak the formula.

In the formula bar add [Symbol] after the underscore (_) and change the type from table to list as shown below. Once you press Enter, you will see that the table objects are now list objects. Each list contains 100 symbols.

And now the final adjustment of the formula:

Wrap the _[Symbol] part with a Text.Combine as shown below. Make sure the second argument is “+” and the type is changed from list to text.

= Table.Group(#"Integer-Divided Column", {"Index"}, {{"Symbols", each Text.Combine(_[Symbol],"+"), type text}})

From here you can proceed with the steps of Part 1, and add the function, invoke it and expand the results.

Thank you Imke for directing me to Text.Combine 🙂

I think it is a great training material.
Follow Me