Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone.
This is my first post and I'm fairly new with Power BI, so I apologize beforehand if I'm asking about something excessively easy or obvious.
I have to tables which I'm looking to merge. Table A holds a record of the invoices, while Table B has costs associated with the invoices. What I want is to have the costs in the same table as the invoices; I tried using a Left Outer type of join through the data in the Number column. The tables look like this:
Example Table A
Example Table B
The problem here is that there are usually serveral rows per invoice in Table A, so when I merge it with Table B, the values get duplicated like this:
I cannot group the rows because I loose other important data from Table A like the product code, which does change per row. So how do I merge both tables in shuch a way that the values from Table B only appear in the first row where there is a coincidence? Is it possible?
Thank you.
Solved! Go to Solution.
Hi @Ale_MCA
It is possible to add the Cost from Table B to only the first row of the same invoice number in Table A, however, I am not sure how you can analyse data after merge the 2 tables in this way...basically, you group Table A with all data as a table, then add the number to the first row, then expand it
#"Grouped Rows" = Table.Group(your previous step, {"your inovice number column"}, {{"allrows", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"your inovice number column"}, TableB, {"your inovice number column"}, "TableB", JoinKind.LeftOuter),
#"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"your cost column"}, {"TableB.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", (OT) => [a=Table.AddIndexColumn(OT[allrows],"Index"),
b=Table.AddColumn(a,"Value",each if [Index]=0 then OT[TableB.Cost] else null)][b])
Hi, @Ale_MCA
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
TableA:
TableB:
You may apply the following transformations on 'TableA'. Here are the m codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADItEMwCQ6VYnWglI9wKjAgpMAYrMEZVYGiApMIErMIEyDTCocKUoAozsApTPCrMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Due = _t, #"Product Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}, {"Due", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if
List.First(
Table.SelectRows(#"Added Index",(x)=>x[Number]=[Number])[Index]
)=[Index]
then List.Sum(Table.SelectRows(#"Table B",(y)=>y[Number]=[Number])[Log Cost])
else
null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Ale_MCA
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
TableA:
TableB:
You may apply the following transformations on 'TableA'. Here are the m codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADItEMwCQ6VYnWglI9wKjAgpMAYrMEZVYGiApMIErMIEyDTCocKUoAozsApTPCrMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Due = _t, #"Product Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}, {"Due", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if
List.First(
Table.SelectRows(#"Added Index",(x)=>x[Number]=[Number])[Index]
)=[Index]
then List.Sum(Table.SelectRows(#"Table B",(y)=>y[Number]=[Number])[Log Cost])
else
null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Ale_MCA
It is possible to add the Cost from Table B to only the first row of the same invoice number in Table A, however, I am not sure how you can analyse data after merge the 2 tables in this way...basically, you group Table A with all data as a table, then add the number to the first row, then expand it
#"Grouped Rows" = Table.Group(your previous step, {"your inovice number column"}, {{"allrows", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"your inovice number column"}, TableB, {"your inovice number column"}, "TableB", JoinKind.LeftOuter),
#"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"your cost column"}, {"TableB.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", (OT) => [a=Table.AddIndexColumn(OT[allrows],"Index"),
b=Table.AddColumn(a,"Value",each if [Index]=0 then OT[TableB.Cost] else null)][b])
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |