Show Records Conditionally In a Data Table

[NewGuy2239]

Hello, 

I am relativley new to power Bi and have a question about how to structure a report. I am going to try to break down exactley what I am looking for in the sections below. Any help would be MUCH appreciated. Thanks!

OVERALL REPORT GOAL:

I have a transactions that I need to show in a table.

There are two types: Normal and split

A normal transaction just needs to show up as it is but a split transaction might be a $100 charge but made of 4 line item charges. What I am trying to do is that if a charge is flagged as "split" that the record itself will now show up on the table BUT its line item charges will. So if I have a $100 split charge, the 4 charges that make the $100 show up instead. 

PROBLEM:

The problem I am having is I am not quite sure how to do this. I get the basics of merging and appending queires but I am not sure what to do to conditionally show these records on a report. 

DATA SETUP:

*For reference I am adding some context with the column structure of the tables involved*

Table 1: main_receipt_table

ID	purchase_date	amount	split
1	11/06/2021	$870	false
2	11/08/2021	$400	true
3	12/13/2021	$23.23	false

Table 2: split_line_items

matched_receipt_id	amount	split_order
2	$150	1
2	$100	2
2	$150	3

END GOAL VISUALIZED

Below is how I would want the new table to show up 

amount	purchase_date	split
$870	11/06/2021	false
$23.23	12/13/2021	false
$150	11/08/2021	true
$100	11/08/2021	true
$150	11/08/2021	true

Above shows that the new table will show the split line items IN PLACE of the main charge.

Please reach out with any questions! And thanks in advance!

[adudani · Accepted solution]

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.