Data value is split into two rows/columns but counted as one distinct value by Power Query/DAX

[Anonymous]

Recently, I have been working with a data model, where I had to include data generated from a Microsoft List and append it to an existing table. This lead to some odd behaviour with the rows from the list, as seen in the picture below. It appears as if Power BI handles the values from the List-data separately from the other data despite using the exact same values.

In #1, PQ shows four values in [Kategori] in the table that is created by appending the List to the non-List data. There appears to be no issues here.

As seen in #2 & #3, when the data is then added to a table or matrix, the one value (= "S") that appears in both the List-data and non-List-data starts splitting off into multiple columns/rows.

Especially odd is the distinct count of [Kategori] values in #3's tables: here, the sum is still four both with and without [Kategori] as the dimension/attribute.

Is this a bug or am I missing something that causes this behaviour?

There were no issues prior to appending the Microsoft List-table to the original table - in fact, I had entered data manually in a separate table before deleting and replacing it with the Microsoft List. It seems to only be related to the List-data, as Measure 1 and Measure 2 calculates the exact amounts present in that data (6.200 and 1.550, respectively).

[v-lionel-msft]

Hi @Anonymous ,

It may be that the two values contain different numbers of spaces.

Try to check the values.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

@v-lionel-msft 

I had some time to look into the issue and discovered it is actually due to the 'Sort by column' function in Power BI and not Microsoft Lists as I thought initially. Essentially, if you configure your columns to sort by another column before appending the tables, you would run into this issue when some data is missing/is not specified. 

@v-lionel-msft Is this intended behaviour? I understand the changes are made in two different places, but it seems as if Power BI should show the error after applying the changes in PowerQuery that causes the issue.

Example with test data:

Looking at the screenshots with my test data, I can append the tables even when the SortOrder_Name and SortOrder_Title are null for the appending table's rows. This results in the visual error where the categories with missing SortOrder-values are shown twice. The error finally appears when I reset the 'Sort by column' and try to set it to sort by SortOrder_Name again.

[v-lionel-msft]

Hi @Anonymous ,

The reason for the error is shown in the figure.

The values of the two columns must be one to one.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft 

The titles are configured to 'Align Right' in the table, hence the apparent difference in length. The space to the left of the value is due to the configuration of the column width.

Additionally, I do believe Power BI does not ignore the spaces in a value when performing a distinct count, thus I do not see it as a possibility. Nonetheless, I added a Text.Trim in PowerQuery and the result is the same as before (see picture underneath).