Hi,
I have a powerpivot table that looks like this:
Order | State | Quantity
A | CA | 10
A | NY | 20
B | CA | 30
C | CA | 40
C | FL | 50
I need to create a pivot table that'd look like this:
ConcatState | Units
CA&NY | 30
CA | 30
CA&FL | 90
Can you please help?
Hm, maybe possible in Power Pivot (but not sure as it looks a bit like recursion and you also don't have an unpivot-function there as well), so why not use Power Pivots ETL engine Power Query to create a lookup-table, which you then connect to your table by ORDER:
Order|ConcatState
A|CA&NY
B|CA
C|CA&FL
So you would drag the field "ConcatStore" from this table into the row section of your pivot table.
This would be one of your code-options:
let
Source = ImportOfYourSourceTable,
LookupTable = Table.Group(Source, {"Order"}, {{"ConcatState", each Text.Combine(_[State], "&")}})
in
LookupTable
Where ImportOfYourSourceTable depends on where your data is stored.
Hi Yan,
According to your description, you need to contact the State which belong to the same order in a Pivot table, right?
In DAX, CONCATENATE function joins two text strings into one text string. The text strings to be joined into a single text string. Strings can include text or numbers. You can also use column references. In your scenario, you need to contact the text in
different rows which cannot be achieved in current version of PowerPivot.
https://msdn.microsoft.com/en-us/library/ee634811.aspx?f=255&MSPPError=-2147217396
We can achieve this requirement by using custom code in SQL Server Reporting Services, please refer to the link below to see Charlie's reply.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/98e3a5f4-5816-465a-aa60-7208e1e41652/how-to-create-a-pivot-report?forum=sqlreportingservices
Regards,
Excel 2010 with free PowerPivot and Power Query Add-In.
Compatible with Office 2013 Pro Plus.
With multiple Sets of multiple States.
Sets can share States.
A typical Many-to-Many Problem as shown in the book:
"Power Pivot Alchemy"
by Collie and Jelen (and Brueckl)
http://www.mediafire.com/view/pkgk6ijnvuzjv4a/09_03_15.xlsx