Triple records after merge and filter

[WannesT]

Hi,

I have a strange phenomenon in power query (power bi).

Scenario:

1. get data from SQL server = 3 tables with data, where the tables has exact the same field names

2. append the 3 tables togheter in a new query

3. get data from an excel file in a new query

4. merge the excel data with the query from step 2, with 2 fields linked to each other 

5. expand the columns of the merged fields

Perfect! I have the data like I need it.

But then something strange happens. When I filter the resulting query of step 5, then every record is tripled, where the values in the field coming from the query from step 2 are the same or empty.

I have no clue what causes this.

Schematic:

After filtering on field A, I get this result, so 3 same records with different values in field Y.

Any suggestions are welcome.

Wannes

[WannesT · Accepted solution]

I have found a solution. Surprisingly it is because there are records in field X with a period in it. For example: Tank4.Inflow. After removing the points, the problem is solved.

I don't know why power query reacts that way on fields with a period in it...

[Vera_33]

Hi @WannesT 

When you append 3 tables in step 2, for the same X= Tank1, do you have 3 rows with different Y values? If yes, then it is expected, you merge in step 4, the little table has 3 rows

[WannesT]

Txs Vera.

No, all the tanks have only one value. There is one record in the merge for each tank. The values I get in field 'Y' are right, but are double or empty or null. In my example: Tank1 = 500 and Tank2 = 10 are the correct values.

[Vera_33]

Hi @WannesT 

You mean after you append 3 tables, each Tank only has one value in Y, but after you merge it auto generates some empty / null values? It's interesting...can you check both tables before merge, the table has X+Y, all distinct combo, the table has X+A+B+C, all distinct combo?

[WannesT]

Yes, al records has distinct combo. And what is weird: only after applying a filter on column A, there are triple records, not before. I cannot understand how those extra records are generated by filtering, unless a bug in power query (dangerous statement, I know).