Presenting 'any' type date within a Pivottable data area

[pjmorris]

Hi,

I have data in a column of document numbers with the form: 'C11-123-xxx-123'. It is of type 'Any' in Powerquery. Yesterday I created a Pivottable with appropriate rows and columns to identify the data (it actually only returns a single entry at each data point). The measure MIN(qrySrc[Doc No]) worked perfectly (honest - I was surprised). Today it returns an error. Perhaps, when I created the table I didn't try to refresh it and that until I did so it was quite happy as I have a correctly populated table to show that it worked.

Does anyone have any idea why it may have worked and now doesn't. Or, does anyone know how I can get the Document Number returned to the data area of the form.

many thanks.

Peter

 

[RoryA]

Assuming you are using the data model and Power Pivot, you can use a CONCATENATEX measure to return text in the values area. I can't see how MIN would ever have worked.

 

[pjmorris]

Hi Rory, I didn't expect MIN to work, but was just searching for something that might - I hadn't come across CONCATENATEX which will be useful. However, I've checked again and following a reboot the MIN function is working as before (it also works with MAX) - so I guess that it is simply picking the first (or last) from the list. The error, I suspect, was that my Excel installation appears to 'fall back' to a previous version from the 2016 when something goes wrong. Its a corporate solution and I believe I've got elements of 2013 still on the machine - it occasionally does weird things! (sounds like flannel, but thats the truth!).

Many thanks for the reply and delighted to find the right way of doing this.

Regards

Peter

 

[RoryA]

following a reboot the MIN function is working as before (it also works with MAX) - so I guess that it is simply picking the first (or last) from the list

That's weird but I shall have to try that - useful if it does work!

 

[pjmorris]

Hi Rory, I tried this measure:

=CONCATENATEX(qryVault[Babcock Doc No],", ")

but I received an error that a single value for the table couldn't be determined, which is odd because a single value would be pointless in this context. Have I missed something?

Edit: Corrected it: =CONCATENATEX(qryVault, [Babcock Doc No],", ") I had the syntax wrong!

 

[pjmorris]

Hi Rory,

It remains weird. MIN really did work as I've just spent an hour demonstrating it to my boss etc. However, now, it doesn't work and rebooting didn't cure it - that said, I've made a few amendments but don't think they should have any affect. I've tried the CONCATENATEX approach, which worked, but when I try to clear a slicer after filtering it refuses to do so - its as if the Concatenation is trying to return too many values and the system simply gives up. Additionally, if I remove the measure from the table I can remove the Slicer filter, but then I can't add the measure back; apply a filter and I can add the measure, but again cant remove the filter. The measure correctly worked across the entire data set when I amended the existing measure to replace MIN with CONCATENATEX. It really feels like a bit of a bug and I'm at a loss to know what to do.

Any thoughts gratefully received.

Regards

 

[RoryA]

What is the current measure, and what do you want returned if the filters are all removed? (I'm guessing you don't really want all the document numbers displayed?)

 

[pjmorris]

HI Rory, I've been working on the measure this morning and it also occurred to me that the problem was trying to concatenate too many records. The measure now looks like this:

IF(COUNTA(qryVault[Doc No])<=16,CONCATENATEX(qryVault,qryVault[Doc No] & "","
"),"Too Many Records" )

The linefeed before the "),"Too Many Records") is deliberate as UNICODE(10) didn't seem to introduce a linefeed as necessary.

Regards

 

[RoryA]

FWIW, your CONCATENATEX functions should really be:

CONCATENATEX(qryVault,qryVault[Doc No],",")

with the third argument being the delimiter.

I take it that is now working OK?

 

[pjmorris]

Hi Rory, yes it seems to be working fine. I think you miss read my measure though: the & "", section simply appends nothing to the qryVault[Doc No] - I know it seems pointless, but when things aren't behaving I'll try anything. The next " mark and the one on the line below actually bound the delimiter which is a newline. I know that too looks odd, but it does work to introduce a linefeed whereas the UNICODE(10) didn't do anything.

Many thanks for your help.

Regards