Forum Discussion
Power Query - Not all "Tables" created equal?
You don't need to do any appending. You can merge and fill up instead.
let x=Count, Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2019-20"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"CC","FY"}), #"Merged Queries" = Table.NestedJoin(Source, {"CC", "FY"}, Dim_ForSort, {"CC", "FY"}, "Dim_ForSort", JoinKind.LeftOuter), #"Expanded Dim_ForSort" = Table.ExpandTableColumn(#"Merged Queries", "Dim_ForSort", {"Dimension Details"}, {"Dimension Details"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded Dim_ForSort",{{"CC", Order.Ascending}, {"FY", Order.Ascending}})), #"Filled Up" = Table.FillUp(#"Sorted Rows",{"Dimension Details"}) in #"Filled Up"
This is an interesting question and I'm curious what the reason is too. My hunch is that it's related to how the internal optimizer builds the query where the slower version doesn't get vectorized optimally (or maybe isn't memoized optimally).
I'd guess there's an approach that's better than either version stated, but I don't fully understand the reasoning behind the transformations in GetMissingDim, so it's hard to suggest an alternative.
Hi Alexis,
In essence - I am doing a vlookup true and find the closest match greater than what is in the dimension table. The intent of the getmissingdim - is that I would append that table to the dimension table to get a complete dim table with all gaps filled.
I understand that there are other ways to do vlookup true (i.e. append then sort then fill (up or down depending on the desired results). Side note, this has similar performance than the above alternative 2 (I tested it this morning (below - I've pasted the code) - but likely a better way to do vlookup true.
But I am still curious to find out why the perfomance difference between alternative 1 and 2 - as I have other scenarios where an append would not be the right approach... interesting your hunch re vectorized vs memorized aspects - I will wait to see if there are other opinions - but that may be the best explanation!
Dim_ForSort
let
x=Count,
Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x),List.Repeat({"Dim detailsY1"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x),List.Repeat({"Dim detailsY3"},x)}),{"CC","FY","Dimension Details"}),
Add_Flag = Table.AddColumn(Source, "flag", each "x")
in
Add_Flag
MissingDim_Append
let
//[
x=Count,
Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2019-20"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"CC","FY"}),
Append_DimForSort = Table.Combine({Source, Dim_ForSort}),
BuffTable = Table.Buffer(Append_DimForSort),
SortedRows = Table.Sort(BuffTable,{{"CC", Order.Ascending}, {"FY", Order.Ascending}, {"Dimension Details", Order.Ascending}}),
FilledUp = Table.FillUp(SortedRows,{"Dimension Details"}),
FilterOutFlagX = Table.SelectRows(FilledUp, each ([flag] = null))
in
FilterOutFlagX
You don't need to do any appending. You can merge and fill up instead.
let x=Count, Source = Table.FromRows(List.Zip({{1..x},List.Repeat({"2018-19"},x)})&List.Zip({{1..x},List.Repeat({"2019-20"},x)})&List.Zip({{1..x},List.Repeat({"2020-21"},x)}),{"CC","FY"}), #"Merged Queries" = Table.NestedJoin(Source, {"CC", "FY"}, Dim_ForSort, {"CC", "FY"}, "Dim_ForSort", JoinKind.LeftOuter), #"Expanded Dim_ForSort" = Table.ExpandTableColumn(#"Merged Queries", "Dim_ForSort", {"Dimension Details"}, {"Dimension Details"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded Dim_ForSort",{{"CC", Order.Ascending}, {"FY", Order.Ascending}})), #"Filled Up" = Table.FillUp(#"Sorted Rows",{"Dimension Details"}) in #"Filled Up"Thanks - that works - I was working based on a different scenario where there was no matching values between the tables (hence the append) - but given that in this scenario, there are matching values - a merge is preferable.
Quick question on your use of buffer - my understanding is that buffer should be used before sorting a table to bring it into memory and ensure a proper sort - but you've included after completing the sort. Is there a reason for doing so? Is my understanding of buffer incorrect?
I want it in memory in the sorted order, so I buffer after the sort.