Forum Discussion
Numbers from custom column not usable
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
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.
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
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.
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