Append two tables based on Matched and Unmatched records

[Learner27]

Hello, everyone,

I have two tables, namely "Table Incorrect" and "Table Correct." My goal is to append these two tables into a single table based on a condition. Specifically, if there are any customer numbers existing in "Table Correct," I need to take the corresponding rows and append them to "Table Incorrect." Additionally, if there are any customers not matched in "Table Correct," I also want to include those rows in "Table Incorrect."

Ultimately, I need to append these two tables. Due to data privacy concerns, I am providing sample data and the final output result in the form of a picture within the PBIX file.


Note: Please use  customer numbers to map.

Please refer to the screenshot below, and I appreciate any valuable suggestions you can provide. This is urgent, and your assistance is highly requested. Please help me.

Append Table.pbix

Thank you.

[Learner27]

Hi All, I have applied this DAX by creating a new calculated table, and it has provided the correct result. However, I now need to implement the same logic in Power Query. This is necessary as I have to disable "Enable Load" on the "Table Correct" in Power Query, as per the requirement.

Please Help to implement same in the Power Query it would be greatly Appreciated

Thank you.

[Daniel29195]

hello @Learner27 ,

from my understanding, you need to append everything that exists in table incorrect and not exists in table correct +  table correct 

if this is the case , you have 2 choices 

option 1 :  power query

as @amustafa suggested 

option 2 :  using dax caluclated table 

new table = 

# get all rows existing in table incorrect and doesnt exist in table correct.
var datasource1   = 

except ( table incorrect  , table correct]) 

# append datasource1 to table correct

var appended_data = union ( datasource1 ,  table correct ) 

return 

appended_data

let me know if this works out for you

best regards

[Learner27]

Hi @Daniel29195 

I had to turn off "Enable Load" in Power Query for the correct table, and I expected to see only one table in the result. However, when I executed the DAX you provided, it returned the same record multiple times. Here are the results:

This is the output that I am looking for.

If you have any suggestions or if there's something I might be missing, please let me know. Thank you.

[Daniel29195 · Accepted solution]

sorry my bad .  @Learner27 

yes the code i wrote will not work . 

i have modified it to this : 

Table 2 =

VAR incorrect = distinct('table incorrect'[customer nb])

VAR correct =  distinct ('table correct'[customer nb])

VAR datasource = EXCEPT(incorrect,correct)

VAR incorrect2 =

FILTER(

    'table incorrect',

    'table incorrect'[customer nb] in datasource

)

VAR appended_data = UNION(incorrect2,'table correct')

RETURN

    appended_data

suppose this this is the output, that you want to achieve.

let me know if this works out for you

best regards

[amustafa]

Hi @Learner27 

Best you do this in Power Query and append the two tables. See the attached pbix file. First you need to create a 0/1 flag (isCorrect) in each table and then do the Append as new query.

Append Table

M Code for Append table...

let
Source = Table.Combine({#"Table Incorrect", #"Table Correct"}),
#"Grouped Rows" = Table.Group(Source, {"CustomerNumber", "CustomerName", "Address", "Date"}, {{"isCorrect", each List.Max([isCorrect]), type nullable number}})
in
#"Grouped Rows"

If I answered your question, please mark this thread as accepted.
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[Learner27]

Hi @amustafa ,

Thank you for your quick response,
I noticed that you appended two tables with out conditions applied, as shown in the image below. I don't want to see the red-highlighted customers and their respective data. I want to see all the customers from the "Table Correct" and all the customers in the "Table Incorrect" that are not matched in the "Table Correct" table along with their respective columns. You directly appended two tables, which was not my requirement. Please refer to the image below for clarification

[amustafa]

Sorry about that. I corrected the Power Query part. You can download the updated pbix file form my folder. Here's the updated M code.

let
Source = Table.NestedJoin(#"Table Correct", {"CustomerNumber"}, #"Table Incorrect", {"CustomerNumber"}, "Table Incorrect", JoinKind.FullOuter),
#"Expanded Table Incorrect" = Table.ExpandTableColumn(Source, "Table Incorrect", {"CustomerNumber", "CustomerName", "Address", "Date", "isCorrect"}, {"Table Incorrect.CustomerNumber", "Table Incorrect.CustomerName", "Table Incorrect.Address", "Table Incorrect.Date", "Table Incorrect.isCorrect"}),

// Add conditional columns for each field
#"Added Conditional CustomerNumber" = Table.AddColumn(#"Expanded Table Incorrect", "Customer Number", each if [CustomerNumber] = null then [Table Incorrect.CustomerNumber] else [CustomerNumber]),
#"Added Conditional CustomerName" = Table.AddColumn(#"Added Conditional CustomerNumber", "Customer Name", each if [CustomerName] = null then [Table Incorrect.CustomerName] else [CustomerName]),
#"Added Conditional Address" = Table.AddColumn(#"Added Conditional CustomerName", "Final Address", each if [Address] = null then [Table Incorrect.Address] else [Address]),
#"Added Conditional Date" = Table.AddColumn(#"Added Conditional Address", "Final Date", each if [Date] = null then [Table Incorrect.Date] else [Date]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Date",{"Customer Number", "Customer Name", "Final Address", "Final Date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Final Date", "Date"}, {"Final Address", "Address"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Customer Number", Order.Ascending}})
in
#"Sorted Rows"

[Learner27]

Hi @amustafa 

Your code is also providing me with the exact result. I have tried to understand it, and even though I attempted to use a left anti join to shorten my code in the existing model, it is still yielding the same result. Can you please confirm which approach is the correct one? I am quite confused.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFPa8MwDMW/isi5hdjO32PTdIfR0rEMeig9eInBYo5dnCxl334yy2ALuVmP97P0pOs1YjGLNtGz0xZqp+jJuICTRAvNSFXMtoxvecxFdNsENw9uaRU0PY6aiiTN4Cw/YDepNb8gsXLvEDoMzlKVFyW8IP3w2gWAL4CExJ3BVkHl3SMAXCSwV530cLRrREpiI73UcNE4hinSLIeD6ecIYuHPSHxzPdRywoHeRRlT4ruhjmbq1oicxEOP5gsuaH5SiCSFCn2rofYBSRZIQeIJWy2VgaMKQ2V5Ac3df1KyeVdLpiTxbHBCCXutQpcyZvCEv8HT/34Wk0gDGZQ97deqYT4fnePhXDcv+A91+wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerNumber = _t, CustomerName = _t, Address = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerNumber", Int64.Type}, {"CustomerName", type text}, {"Address", type text}, {"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "isCorrect", each 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"isCorrect", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"CustomerNumber"}, #"Table Correct", {"CustomerNumber"}, "Table Correct", JoinKind.LeftAnti),
#"Appended Query" = Table.Combine({#"Merged Queries", #"Table Correct"})
in
#"Appended Query"

Please let me know and thank you for Quick response