Forum Discussion

Stealth02's avatar
Stealth02
Helper I
3 years ago
Solved

Power Query - Not all "Tables" created equal?

Context: I have gaps in my master data - where I have cost centers in my fact table that are not present in my master data for particular fiscal year - but those cost centers are present in other yea...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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"
AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

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"
Stealth02's avatar
Stealth02
Helper I
3 years ago

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?