let me have some doubts about this statement. In any case, for those wishing to try the different times of the two different solutions, you can use this script that I got from Ben Gribaudo's blog.
Obviously the results can vary a lot depending on many things. But you can test the performance of these scripts for different dimensions of the dataset
let
//Get the current time, which will be the query start time
StartTime = DateTime.LocalNow(),
//Make sure StartTime is evaluated just before we request data from Facebook
trig = if StartTime<>null
then
#"toAndFromCols LDS"
else
null,
//Find the number of rows returned
NumberOfRows = Number.ToText(Table.RowCount(trig)),
//Get the current time, which will be the query end time
EndTime = DateTime.LocalNow(),
//Make sure PQ evvaluates all expressions in the right order:
//first, get the Number of rows, which ensure that
//the Source expression is evaluated, and in turn StartTime is evaluated
//second, the EndTime is evaluated as part of the duration calculation
Output = "Query analized " & NumberOfRows & " rows and took "& Duration.ToText(EndTime - StartTime)
in
Output
let
//Get the current time, which will be the query start time
StartTime = DateTime.LocalNow(),
//Make sure StartTime is evaluated just before we request data from Facebook
trig = if StartTime<>null
then
#"listgenerate LDS"
else
null,
//Find the number of rows returned
NumberOfRows = Number.ToText(Table.RowCount(trig)),
//Get the current time, which will be the query end time
EndTime = DateTime.LocalNow(),
//Make sure PQ evvaluates all expressions in the right order:
//first, get the Number of rows, which ensure that
//the Source expression is evaluated, and in turn StartTime is evaluated
//second, the EndTime is evaluated as part of the duration calculation
Output = "Query analized " & NumberOfRows & " rows and took " & Duration.ToText(EndTime - StartTime)
in
Output
listGenerate LDS:
let
Source = LDS,// Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATETIME", type datetime}, {"CATEGORY", type text}, {"CUMULATIVE VIEWS", Int64.Type}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"CATEGORY", Order.Ascending}, {"DATETIME", Order.Ascending}})),
listCategory = List.Buffer(#"Sorted Rows"[CATEGORY]),
listCumulative = List.Buffer(#"Sorted Rows"[CUMULATIVE VIEWS]),
listCount = List.Count(listCategory),
listCumulativeDifference =
List.Skip(
List.Generate(
()=> [varCategory = listCategory{0}, varDiff = 0, Counter = 0],
each [Counter] <= listCount,
each
try
if [Counter] = 0
then [varDiff = listCumulative{[Counter]}, Counter = [Counter]+ 1]
else if listCategory{[Counter]} = listCategory{[Counter] - 1}
then [varDiff = listCumulative{[Counter]} - listCumulative{[Counter] -1}, Counter = [Counter]+ 1]
else [varDiff = listCumulative{[Counter]}, Counter = [Counter]+ 1]
otherwise [Counter = [Counter] + 1],
each [varDiff]
),
1
),
CombinedColumns =
Table.ToColumns(#"Sorted Rows") & {listCumulativeDifference},
BackToTable =
Table.FromColumns(
CombinedColumns,
Table.ColumnNames(#"Sorted Rows") & {"Difference"}
)
in
BackToTable
toAndFromCols LDS:
let
Origine = LDS,
decumul =(cumulist)=>
let
ttc=Table.ToColumns(cumulist),
names=Table.ColumnNames(cumulist),
tfc = Table.FromColumns(ttc&{{0}&cumulist[CUMULATIVE VIEWS]},names&{"shifted"}),
ac = Table.AddColumn(tfc, "dec", each [CUMULATIVE VIEWS]-[shifted]),
rl = Table.RemoveLastN(ac,1)
in
rl,
#"Raggruppate righe" = Table.Group(Origine, {"CATEGORY"}, {"all", each decumul(_)}),
#"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"DATETIME", "CUMULATIVE VIEWS", "HOURLY VIEWS (POWER QUERY CALCULATION)", "dec"}, {"DATETIME", "CUMULATIVE VIEWS", "HOURLY VIEWS (POWER QUERY CALCULATION)", "dec"})
in
#"Tabella all espansa"
LDS:
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRBagMxDEWvEmYdiKWxR3Z2cY8RssgidFko3fT2HWtwodUXaGNIeBjP/9K73xdqF6ILJ2qnRFeSK/NyXt6eX6/3j8/v023/wdtxPM5/cXbwOg5Dr5heE6SzQ5f9KIYuSpOh235kQ2/47jz+WQ0tDi3wJRXTZd0Pm2DD9DYykf80JUzLoCkZ3GlTGAVOTplSUCjklCkwcHLKrDBwKg7dYChOmU1DMbRTZoNjRRWPFSWGT3HapAQz5OTcTmNWqsHJwRkOCzt9EsNPZadQWjd9kplcMFz9ICEOXqM4o5aQKpSGOSJVDFqjsisHVKF0hneD6epTWvZuMF1Kw41DqlC6RlXRp4ZaRBWDLgWNFjKF0hI1RZ/SCplC6Rw1hdISNcWgJUdN0afhQqYYdE1RUygtUVH06ayQJ5SuUU30XwmFNHHsMfxOpIljj+feP34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATETIME = _t, CATEGORY = _t, #"CUMULATIVE VIEWS" = _t, #"HOURLY VIEWS (POWER QUERY CALCULATION)" = _t]),
mt= Table.TransformColumnTypes(Origine,{{"DATETIME", type datetime}, {"CATEGORY", type text}, {"CUMULATIVE VIEWS", Int64.Type}, {"HOURLY VIEWS (POWER QUERY CALCULATION)", Int64.Type}}),
ttc=Table.ToColumns(mt),
names=Table.ColumnNames(mt),
largeDS= List.Transform(ttc, each List.Repeat(_,5000)),
tfc=Table.FromColumns(largeDS,names)
in
tfc
