Transpose Columns with a twist

rbtmp · Nov 27, 2016

Hi

I really would appreciate expert help in solving this puzzle in Power Query (or Power Pivot?)

I have following input table, which list customer names & employees assigned to it. I would like to create a cross tab view/table which only results in one customer on one line with all the various roles across the column. Challenge is there could be more then on similar role/employees assigned to same customer. in that case I would like to concatenate the results in single cell separated by "/"

Its similar to expecting multiple lookup values in single cell in excel. Following is just a sample , there are way more then 3 roles & employes listed below.

Input
Customer	\|	Sales Role	\|	Rep name
ABC Corp	\|	Manager	\|	Joe
ABC Corp	\|	Technician	\|	Linda
ABC Corp	\|	Technician	\|	Jane
XYZ	\|	ISR	\|	Peter
XYZ	\|	Technician	\|	Linda
XYZ	\|	Manager	\|	Mike
XYZ	\|	ISR	\|	Ron


Expected Output
Customer	\|	Manager	\|	Technician	\|	ISR
ABC Corp	\|	Joe	\|	Linda/Jane	\|
XYZ	\|	Mike	\|	Linda	\|	Peter/Ron

really appreciate some guidance
Excel 2013 with power Query & Power pivot add-in loaded on Windows 7

Thanks

Comfy · Nov 28, 2016

Try this:

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Customer", "Sales Role"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Rep Name", each Text.Combine(_[Rep name], "/ "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows","Merged",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"Customer", "Sales Role"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[#"Sales Role"]), "Sales Role", "Rep Name")
in
    #"Pivoted Column"

Owen Auger · Nov 28, 2016

If you are looking for a PowerPivot solution, the CONCATENATEX function would have been perfect for this.
Unfortunately it isn't available in Excel 2013 (just Excel 2016 and Power BI Desktop).

In Excel 2013 you would have to write an ungainly measure like this, which would have to be limited to a certain number of Reps to concatenate:

Code:

=
SWITCH (
    DISTINCTCOUNT ( Data[Rep name] ),
    1, VALUES ( Data[Rep name] ),
    2, FILTER (
        VALUES ( Data[Rep name] ),
        RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 1
    )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 2
        ),
    3, FILTER (
        VALUES ( Data[Rep name] ),
        RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 1
    )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 2
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 3
        ),
    4, FILTER (
        VALUES ( Data[Rep name] ),
        RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 1
    )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 2
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 3
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 4
        ),
    5, FILTER (
        VALUES ( Data[Rep name] ),
        RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 1
    )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 2
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 3
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 4
        )
        & "/"
        & FILTER (
            VALUES ( Data[Rep name] ),
            RANKX ( VALUES ( Data[Rep name] ), Data[Rep name],, ASC ) = 5
        ),
    "6+ Reps"
)

Sample model here:
https://www.dropbox.com/s/6pxsduqspor5d2x/Transpose columns with a twist.xlsx?dl=1

Transpose Columns with a twist

rbtmp

New member

Comfy

New member

Owen Auger

New member