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"
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.
Super thank you.
I will accept your original explanation as the solution - as it is likely the most probable answer. Thanks for all the feedback.