Summarize returns "duplicates"

[Anonymous]

What I'm trying to do:

1. Use DAX Summarize to group a table by two text columns like this: D_BatchID = SUMMARIZE(F_StockEvent, F_StockEvent[ItemID], F_StockEvent[BatchID])

2. Create a new column to that table by combining the two existing columns, separated by "¤", like this: 

ItemID¤BatchID = D_BatchID[ItemID]&"¤"&D_BatchID[BatchID]

3. Use this column to create one-to-many relationship.

The problem is that when I use Summarize, the resultant table has some rows that look equal but are treated as different. After I combine the columns to form a new column, the resultant rows are actually equal and therefore cannot be used to create one-to-many relationship. See the images for clarification:

ItemID-BatchID, 4 distinct valuesBatchID, 5 distinct valuesItemID, 1 distinct values

As can be seen from the screenshots above, the BatchID column has 5 distinct values but when combined with the ItemID column (that has only one distinct value), the resultant ItemID¤BatchID-column only has 4 distinct values. I also created a new column for debugging purposes: Column = LEN(BatchID[BatchID]). This returns blank for one of the rows and 0 for the other, which I suppose means that the first one is a blank string and the other one is an empty string, which are not the same thing, but when combined with another string the result for both is the same? The question is: how can I avoid this? I want a table that only has one row for each ItemID-BatchID combination, and for my purposes blank and empty are the same thing.

[v-piga-msft]

Hi @Anonymous,

---

This returns blank for one of the rows and 0 for the other, which I suppose means that the first one is a blank string and the other one is an empty string, which are not the same thing, but when combined with another string the result for both is the same? The question is: how can I avoid this? I want a table that only has one row for each ItemID-BatchID combination, and for my purposes blank and empty are the same thing.

---

I have made a test to reproduce your scenario but I cannot reproduce it.

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[Anonymous]

Here's a dummy pbix which has the same scenario: https://bit.ly/2w69NdP

[v-piga-msft]

Hi @Anonymous,

By my tests, it may caused by your data source.

When I copy your table to Excel and get data with Excel, create the calculated table with SUMMARIZE. It show like below.

If I confirm each blank value in BatchID is null. It will show like below.

It seems that you may have a space in BatchID, so you will get the issue.

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hey @v-piga-msft,

Thanks for the help. I played around with the query editor and managed to fix the problem using Table.ReplaceValue, replacing null values with empty values (Table.ReplaceValue(F_StockEvent_dummy_table,null,"",Replacer.ReplaceValue,{"BatchID"})). There was no space character in the BatchID, it was just empty while other rows had null.