HOW TO CONNECT 3 COLUMNS IN ONE TABLE FROM ONE TABLE SOURCE

[Lucy_Quyen1608]

Dear all,

I'm just a newbie to Power Query and honestly struggled to much with them .Currently  I have to find the difference row by row in Table below:

1.  Table name : Document ID

DocumentID	DocumentID_1	DocumentID_2	Amount	Amount_1	Amount_2	Difference
118723		270692	100		50	???
159615	159615		150	140
165484	349610		150	260
343318	343364	343363	150	30	120

where : DocumentID ,DocumentID_1,DocumentID_2 are being connected to DocumentID in each tables : REVENUE, VAT and DATA. 

Here is my simple relationship chart.

My question is how to get the Amount of each DocumentID when they are in the same table.

Hope to get all your support to solve this issue.

[edhans]

Use explicit measures, don't drag fields into the visual.

So instead of dragging the Amount field from your Revenue table, create a measure that is 

Revenue Amount = SUM(Revenue[Amount])

Do that for all three. Then create a 4th measure (doesn't matter where you put it) that is

Difference = [Revenue Amount] - [VAT Amount] - [Data Amount]

[Lucy_Quyen1608]

Dear Edhans,

Thank you so much for your advice. With Document ID table with only 1 Column (DocumentID), I follow your instruction and get the result. 

But when I moved to another table called" VOIDED FCR" which have 3 Columns and they are all primary  keys and some of them are null .  

For example: Document ID & Document1 got value while Document 2 is null

At this stage , it seems that we counld't create relationship between voided FCRs and the rest three tables : REVENUE,DATA,VAT since we have 3 primary keys existed in the same table .

Hope to receive your help on this issue.

[v-kelly-msft · Accepted solution]

Hi  @Lucy_Quyen1608 ,

Using below M codes to get a transformed table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzBDQAhCAR74e0DWER9WcjF/ts41PNiQsIAszwPidSioERRWtibBgjz3uTZe+80Uqi5ueR5PrB4KmJfYIuerVpMsBD5FtVvEQZIXSLgdgC/jvVcV2aMFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DocumentID = _t, DocumentID_1 = _t, DocumentID_2 = _t, Amount = _t, Amount_1 = _t, Amount_2 = _t, Difference = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DocumentID", Int64.Type}, {"DocumentID_1", Int64.Type}, {"DocumentID_2", Int64.Type}, {"Amount", Int64.Type}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Difference", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"DocumentID_1", "DocumentID_2", "Amount_1", "Amount_2"}),
    Custom1 = #"Changed Type",
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"DocumentID", "DocumentID_2", "Amount", "Amount_2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"DocumentID_1", "DocumentID"}, {"Amount_1", "Amount"}}),
    Custom2 = #"Changed Type",
    #"Removed Columns2" = Table.RemoveColumns(Custom2,{"DocumentID", "DocumentID_1", "Amount", "Amount_1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"DocumentID_2", "DocumentID"}, {"Amount_2", "Amount"}}),
    Custom3 = Table.Combine({#"Removed Columns",#"Renamed Columns",#"Renamed Columns1"}),
    #"Removed Columns3" = Table.RemoveColumns(Custom3,{"Difference"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

And you will see:

Then you only need connect one column.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!