Show Records Conditionally In a Data Table

Hi NewGuy2239 ,

Using paste the following to 3 blank queries:

main_receipt_table (query1) :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI01Dcw0zcyMAJxVCzMDYCUm6NPsKtSrE60khFUhQVchYkBSEVIUChEgTFIgZG+oTFcgZGxnpExwpBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, purchase_date = _t, amount = _t, split = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([split] = "FALSE")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"amount", Currency.Type}}),
#"Appended Query" = Table.Combine({#"Changed Type", TabletoAppend}),
#"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"ID"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"split", type text}})
in
#"Changed Type1"

(split_line_items) query2 :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUVIxNDUAUoZKsTowAQOQgBGSAFiFsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [matched_receipt_id = _t, amount = _t, split_order = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"matched_receipt_id", Int64.Type}, {"amount", Currency.Type}, {"split_order", Int64.Type}})
in
#"Changed Type"

TabletoAppend (Query3)  :

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI01Dcw0zcyMAJxVCzMDYCUm6NPsKtSrE60khFUhQVchYkBSEVIUChEgTFIgZG+oTFcgZGxnpExwpBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, purchase_date = _t, amount = _t, split = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"purchase_date", type text}, {"amount", Currency.Type}, {"split", type logical}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([split] = true)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"ID"}, split_line_items, {"matched_receipt_id"}, "split_line_items", JoinKind.LeftOuter),
#"Expanded split_line_items" = Table.ExpandTableColumn(#"Merged Queries", "split_line_items", {"matched_receipt_id", "amount", "split_order"}, {"matched_receipt_id", "amount.1", "split_order"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded split_line_items",{"amount"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"amount.1", "amount"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"split_order", "matched_receipt_id", "ID"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"amount", "purchase_date", "split"})
in
#"Reordered Columns"

Steps taken:

1. Created a duplicate of main_receipt_table as "TabletoAppend".
2. In the main_receipt_table, filtered split = False.

3. In the table to append, filtered split = true.

4. Merged with split_line_items query on the ID and match receipt columns and made transformations.

5. Finally, appended this on the main receipt table which is filtered.

Appreciate a thumbs up if this helps.

Please accept this as the solution if your query is resolved.