Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to 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!
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]
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear 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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.