cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lucy_Quyen1608
Regular Visitor

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

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

 

DocumentIDDocumentID_1DocumentID_2AmountAmount_1Amount_2Difference
118723 270692100 50???
159615159615 150140  
165484349610 150260  
34331834336434336315030120 

 

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

 

Here is my simple relationship chart.

 

Document ID.PNG

 

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.

 

1 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:

vkellymsft_0-1632464088096.png

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!

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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]


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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 .

VOIDED FCR.PNG

Hope to receive your help on this issue.

 

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:

vkellymsft_0-1632464088096.png

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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors