Concatenate values

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?

September 2nd, 2015 2:34pm

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.

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 3:22pm

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,

September 2nd, 2015 11:03pm

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

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 10:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics