I have an excel sheet that has 3 columns, all if which contain string data and I need to create pivot table out of those 3. By default when we drag sting column in values section for creating a pivot, excel aggregates it to show count(numbers), but I need to show values(actual values in that column). I googled it and found out that we can do it in excel in this tutorial - https://www.contextures.com/pivottabletextvalues.html
Also while creating pivot, if we check on ‘Add this data to Data Model’ checkbox, then it allows us to create a new measure with DAX formula. I used the following formula for creating a custom measure on the column that I’ll add in values -
= CONCATENATEX(Range, Range[column3])
Adding this newly created measure to values section made excel show values in pivot and not count.
So my doubt is, can we automate all of this in C# code? I mean when I create a worksheet with data, can I also create a pivot table from that worksheet and add it to data model, create a dynamic measure and add it to values?
Sorry for such a long question and please let me know we anything else is needed from my side.