Can Pivot Table 'Values' show text?

Question

I have data like below, 'function', 'item' and 'updated part' can have many different values, while 'area' would only have 'app' or 'sp' value.

Data

Function	Item	Area	Updated Part
function a	item 1	app	app1
function a	item 2	sp	sp1
function b	item 3	app	app2
function b	item 4	sp	sp2

I would like to transform / summarize it into below,

Function	Item	app	sp
function a	item 1	app1
	item 2		sp1
function b	item 3	app2
	item 4		sp2

Is it possible to do it through Pivot table?

I tried but the 'Values' area will always do count / sum function and could not display original text.

===============================================================

Update on 2024-06-04: For one item, there could be more than one Area & Updated Part. For example,

Data

Function	Item	Area	Updated Part
function a	item 1	app	app1
function a	item 1	app	app2
function a	item 2	sp	sp1
function b	item 3	app	app2
function b	item 4	sp	sp2

When running the Power Query, there will be an error on the row of "function a",

image1

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
    [List]

Does this mean it could not support multi-item? Thank you.

Code used:

    let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//set all the column data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Function] <> null and [Function] <> ""),

//Pivot the "Area" column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Area]), "Area", "Updated Part"),

//to blank repeated functions:
    //add shifted column
    #"Add Shifted function" = Table.FromColumns(
        Table.ToColumns(#"Pivoted Column")
        & {{null} & List.RemoveLastN(#"Pivoted Column"[Function])},
        Table.ColumnNames(#"Pivoted Column") & {"Shifted Function"}),

    //Blank if shifted <> function
    #"Null Repeats" = Table.ReplaceValue(
        #"Add Shifted function",
        each [Function],
        each [Shifted Function],
        (x,y,z) as nullable text => if y <> z then y else null,
        {"Function"}),
        
    #"Removed Columns" = Table.RemoveColumns(#"Null Repeats",{"Shifted Function"})

in
    #"Removed Columns"

Answer 1

Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac) can pivot data with no aggregation, allowing text results.

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

It is basically three steps of code:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//set all the column data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Pivot the "Area" column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Area]), "Area", "Updated Part")
in
    #"Pivoted Column"

If you want to "blank" the repeated Function cells, you can use conditional formatting, or you can add a few lines of M-Code in Power Query:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//set all the column data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Pivot the "Area" column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Area]), "Area", "Updated Part"),

//to blank repeated functions:
    //add shifted column
    #"Add Shifted function" = Table.FromColumns(
        Table.ToColumns(#"Pivoted Column")
        & {{null} & List.RemoveLastN(#"Pivoted Column"[Function])},
        Table.ColumnNames(#"Pivoted Column") & {"Shifted Function"}),

    //Blank if shifted <> function
    #"Null Repeats" = Table.ReplaceValue(
        #"Add Shifted function",
        each [Function],
        each [Shifted Function],
        (x,y,z) as nullable text => if y <> z then y else null,
        {"Function"}),
        
    #"Removed Columns" = Table.RemoveColumns(#"Null Repeats",{"Shifted Function"})

in
    #"Removed Columns"

Edit for multiple entries for each item

Add Custom Function

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotNoAggregation

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Use it like this:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//set all the column data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Pivot with no aggregation
    Pivot = fnPivotNoAggregation(#"Changed Type","Area","Updated Part"),
    //to blank repeated functions:
    //add shifted column
    #"Add Shifted function" = Table.FromColumns(
        Table.ToColumns(Pivot)
        & {{null} & List.RemoveLastN(Pivot[Function])},
        Table.ColumnNames(Pivot) & {"Shifted Function"}),

    //Blank if shifted <> function
    #"Null Repeats" = Table.ReplaceValue(
        #"Add Shifted function",
        each [Function],
        each [Shifted Function],
        (x,y,z) as nullable text => if y <> z then y else null,
        {"Function"}),
        
    #"Removed Columns" = Table.RemoveColumns(#"Null Repeats",{"Shifted Function"})

in
    #"Removed Columns"

Answer 2

You will need to create a measure to do this:

enter the formula

=CONCATENATEX(Table2,Table2[app],";")

Now add the new measute "appt" to your Pibot Values

Answer 3

You can do this in a pivot table, but like lots of tricky pivots, you would need to transform your data first (into a numeric option).

In this case, App/SP sounds like a category, so we put that into the pivot column, and we create a new variable 'App/SP Value' to use as the value. However, this may or may not work; it depends on whether there are duplicate entries. This is unclear from your data example.