Combining Columns

Power Query
1

Hi

I have a data table in Power Query. I want to have only one line for each Customer with Appeal A (if they have one) and Appeal B (if they have one).

For example:

image

should end up looking like:

image

Every Customer has at least either Appeal A or Appeal B and many have both.

Thanks

Appeal Sample.pbix (16.4 KB)
Appeals.csv (35.8 KB)

2

Below is my solution:

let
    Source = Csv.Document(File.Contents("C:\Users\m.baniasadi\Downloads\Appeals.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    GroupedRows = Table.Group(PromotedHeaders, {"Customer ID"}, 
                                {{"Data", each 
                                [unpivot=Table.UnpivotOtherColumns(_, {"Customer ID"}, "Attribute", "Value"),
                                filter=Table.SelectRows(unpivot, each ([Value] <> "")),
                                pivot=Table.Pivot(filter, List.Distinct(filter[Attribute]), "Attribute", "Value")
                                ][pivot],
                                type table [Appeal A=text, Appeal B=text]
                                }}),
    Expand = Table.ExpandTableColumn(GroupedRows, "Data", {"Appeal A", "Appeal B"}, {"Appeal A", "Appeal B"})
in
    Expand

Appeal Sample.pbix (28.0 KB)

3

Another solution could be as follows:

let
    Source = Csv.Document(File.Contents("C:\Users\m.baniasadi\Downloads\Appeals.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Pheader = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Group = Table.Group(Pheader, {"Customer ID"}, {{"Count", each  Table.FromColumns(List.Transform(Table.ToColumns(_),each List.Distinct(List.RemoveItems(_,{""}))),Table.ColumnNames(Pheader)), type table [Appeal A=nullable text, Appeal B=nullable text]}}),
    Expand = Table.ExpandTableColumn(Group, "Count", {"Appeal A", "Appeal B"}, {"Appeal A", "Appeal B"})
in
    Expand
4

The last and easiest solution with just UI is:

let
    Source = Csv.Document(File.Contents("C:\Users\m.baniasadi\Downloads\Appeals.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Pheader = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Unpivot = Table.UnpivotOtherColumns(Pheader, {"Customer ID"}, "Attribute", "Value"),
    FilterBlank = Table.SelectRows(Unpivot, each ([Value] <> "")),
    Pivot = Table.Pivot(FilterBlank, List.Distinct(FilterBlank[Attribute]), "Attribute", "Value")
in
    Pivot