Ambiguity in relationships due to zero values and empty strings

[seabassk]

Hey everyone,

I've been reading a lot of reading in here and a lot of the tips and solutions posted here were very helpful for me in creating some solutions in Power BI, so thank you for that :).

I've now come across an issue I can't resolve: Some of the data used in Power BI comes from two Excel tables. My colleagues import a lot of data into that table, and also change entries manually. For this reason, I designed many of the cells in the table to have a formula that does not return a value (e.g., IF(Othersheet!AB12<>""; Othersheet!AB12; ""). I do this to make working with the table more comfortable to my colleagues and to avoid cells displaying zero values or #NV type errors. This only affects an entire row at a time. So either all columns in a row are complete with actual values, or the whole row only has null values or empty strings.

This has not been a problem in Power BI so far, except for now when I need to create a relationship between two of these tables/columns. Since Power BI imports all of the cells in the table, also the ones that have no value or a zero value but just a formula, the columns have a lot of "null" or "" values in Power BI. Of course these values are ambigous and so I cannot map relationships between those two tables (columns). Is there any way around this? Possible approaches I can think of:

1. Keep Power BI from querying those rows that have no values but just empty/null cells

2. Exclude those Rows or values from the relationship mapping

3. Even though this is off topic here, an approach that lets me have the same effect in excel without having all those pseudo-empty rows would also help 😉

Thank you in advance for your input and best regards!

[MFelix · Accepted solution]

Hi @seabassk,

On the advance query filter out the zero / empty values on the column you are refering and this will filter the information from your Data, so giving the 1st option in your post.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @seabassk,

On the advance query filter out the zero / empty values on the column you are refering and this will filter the information from your Data, so giving the 1st option in your post.

Regards,

MFelix

[seabassk · Accepted solution]

Thank you @MFelix, this worked nicely so far. I used the filter option of the column in the query editor. This gave me the follwing code:

= Table.SelectRows(#"Changed Type3", each ([#"Order-ID"] <> null and [#"Order-ID"] <> ""))

This should, in the future when new rows are added with new "Order-IDs" display and include these Order IDs and the repsective Rows, of course unless they are null or "", right?

[MFelix]

Hi @seabassk,

This will work in the future also when new rows are added because in the query view you are saving a number of steps that are repeated in a certain order when you update your data source, so when you add the new rows this will continue to remove the null or blanks, it's like a macro.

Regards,

MFelix

[seabassk]

Thank you @MFelix!