power bi removes records after removing columns?

[Mr_Niels]

Hello all,

my apologies, I posted just too soon, since I have just found the problem myself already. Thanks to whoever spend time already and sorry for the inconvenience.

I was hoping to find a sort of VLOOKUP solution in Power Query to my problem, but after reading through some similar posts, they all turned out to differ from what I need. In the end I thought to have found a solution, however, Power BI behaves in a way I don't understand. Maybe it's a bug, but since I'm very new to Power BI, I'm more likely to assume I did something in a way I'm not really supposed to.

What is the situation? I have a table originating from a .CSV file (unfortunately that is the only source at hand at the moment), but one that is very unstructured. Essentially it is set up as below (though much more extensive):

Column E is the column I need (and don't yet have). Columns A till D is what I have to work with. Essentially I need to be able to use the Customer Hierarchies in my report filters and as such, I don't want to use the numbers, I need their names. I'm trying to solve this in one query and so far everything was going well.

I used the merge queries option:

Set Table 1 and Table 2 to be the same obviously (there is only one table), the column for Table 1 is column C in the example. The column provided for Table 2 is column A. I choose a Left Outer join (all from first, matching from second).

This provides me with an additional column with tables which I just need to extract. However, this is where things get fishy...

When extracting this column, all I need is this one additional column with the Customer Hierarchy Names (in the picture: "Bedrijfsnaam").

[Klantnummer] = Customer Number and [KH] = Customer Hierarchy

When extracting just the column with the names, all my records are removed, where there is no Customer Hierarchy Number in column C present? When I leave the two preceding columns, I do keep all rows. So I leave the two rows, rename my new column (with the customer Hierarchy Names) as desired and as a last step, I just want to remove the two redundant columns, but...

you guessed it, removing these redundant columns also removes all my rows again. What am I doing wrong?

Edit: It turns out that Power BI sort of auto-sorts after extracting the column. This resulted in all the "null-values" ending up outside of the preview scope in the query editor. In the actual report they do show. I now also see I can just remove the other two columns. Now if I can just figure out how to get this problem to be marked: solved...?

Thank you for your help and patience!

Best regards,

Niels

[arify · Accepted solution]

You can reply yourself with the part you wrote in "Edit", and mark that as the solution 🙂