Numbers from custom column not usable

[RomanMdS]

I have a custom column which I can't use in views, while I can still use others.
It is a custom column on a MSSQL query which is a merged query from a seperate CSV query, i.e. I merged the csv query into the MSSQL query. There I expanded the custom column, renamed it and now it's a normal Decimal Number column:

E.g. the "PlanningDuration" column I can use perfectly well, but the one coming from the merged query can't be used. Here's an example:

Clustered column chart with the "PlanningDuration":

And the result when I replace the Values with AlgorithmCalcDuration:

Same issue with all views. As if the values are "null" or alike, even though the Query Window shows the data.

This issue happens on Power BI Desktop.

Thanks for your help!

Kind regards,

Roman

EDIT:
Here's also a video displaying the issue:

[RomanMdS]

Awesome, thanks! I was neither aware of the case sensitivity nor that the merge didn't take place properly (because the preview showed the results of the merge).e

Thanks for your patience with this issue!

[BA_Pete]

No problem, glad we got there in the end.

I knew it would be something really simple, but also very difficult to see without having the actual PBIX to play with.

Have a great weekend 🙂

Pete

[RomanMdS]

I managed to find a way to remove the data source (There are unapplied changes now).

Here's a link to the pbix: https://kepsto.blob.core.windows.net/public/PowerBI_Query_issue.pbix 

Thanks!

[RomanMdS]

I made a video to display the current setup. You might see something that I miss:

[BA_Pete]

Hi @RomanMdS ,

A point of generally-accepted practice which should help you here: You should always explicitly define your values in measures rather than use column values.

Create a measure something like this and use that in the visuals instead:

_algCalcDuration = SUM(Query2[AlgorithmCalcDuration])

// You can replace SUM with AVERAGE/MIN/MAX etc., whatever works best for your scenario

Other things to check:

- Make sure you have refreshed your whole report

- Make sure there aren't any visual/page-level filters restricting what you see

- Make sure any relationships between tables used in visuals are in place and set up correctly

- Make sure you haven't accidentally filtered one of the merged tables in Power Query and not refreshed the preview

Pete

[RomanMdS]

Thanks for your reply!

• I used an Average measure, as suggested
• I refreshed the whole report
• I switched off filters for the visual with the new measure 
• I refreshed all data again, and refreshed the previews (all fine, even with new data)

None of this helped. What more can I do?

What exactly should I check for "Make sure any relationships between tables used in visuals are in place and set up correctly"?

Thanks,

Roman

EDIT: Also, wenn I use the data from the query individually (i.e. not in the merged column context over the other query), then I can work with the data. So the data is really there.

[BA_Pete]

Hi @RomanMdS ,

+2 kudos for the excellent video!

To answer your previous post first:

- The checking of relationships isn't actually relevant in your scenario as you're using all fields from the same table, so no issue there.

- That 'Keep all filters' toggle doesn't do what you think it does: this controls what filters are carried over to a drill through page when using the drill through option on a visual.

On to the issue at hand: This is indeed a funny one. I think the next things I would do would be:

- Create a new page, add a large column chart visual with default formatting to it, then add in the fields that you want. This will ensure that it is neither the visual size, nor the formatting, that is preventing you from seeing the data.

- I would also remove the nested columns ([PlanungStat], [PlanungStatHistory]) from your query in PQ. I'm surprised PQ didn't shout at you for trying to apply them to your data model as PBI really doesn't like them.

Beyond this, I'd probably need to see the actual PBIX to be honest. I'm sure it's something REEALLY simple that we're both missing, but would need to have a 'fiddle' with the file to work it out. Have you tried switching it off and back on again?

Pete

[RomanMdS]

Thanks for the kudos and the clarification!

• I created a new page, and tried different new huge charts, but without success
• I restarted PBI (then refreshed data)
• I use the ([PlanungStat], [PlanungStatHistory]) as intermediate columns because I perform a Json.Document([PlanungStatistic]). Then I use the generated Records for other custom columns. I wanted to avoid decoding JSON for every single column. But now that I think about it custom columns are computed once, so it shouldn't matter. I therefore removed them.

Unfortunately, none of those steps helped. 

When I share the pbix will I also share the data sources? I'm trying to find a way to remove the data sources (but keep the data), then I'll gladly share.

[BA_Pete · Accepted solution]

Ok. So the PBIX you've provided doesn't exactly match the one in the video.

When I opened the PBIX, I noticed straight away that your [algorithmCalcDuration] field in the Data view was empty, suggesting that this data is not actually leaving Power Query and getting to the data model:

Based on this finding, I looked into the key fields used for the merge, as it appeared as though the merge hadn't actually completed. I found that your text casing between Query2[id] and algorithmExecutionTimes[planningGuid] did not match i.e. [planningGuid] was lower case and [id] was upper case.

I added a step into algorithmExecutionTimes to make the [planningGuid] upper case:

= Table.TransformColumns(chgTypes,{{"planningGuid", Text.Upper, type text}})

I was then able to produce this:

Power Query/M code is ENTIRELY case sensitive, both in it's code and the values that it handles. By matching the text cases between the two ID columns, Power Query now recognised them as matching values and was able to complete the merge.

PBIX attached with working output.

Pete