Combining Columns (unable to reply)

[HufferD]

I was unable to reply to this post directly [@CustomerService?] so I’ve added it here:

@KieftyKids:

Continuing the discussion from Combining Columns:

@KieftyKids

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:

should end up looking like:

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

If the pattern is always that if one column has values the other does not, then you can simply select both columns and under the “Add Column” click “Merge Columns”:

[KieftyKids]

Hi @HufferD

Thanks for the suggestion.

Unfortunately, I’m trying to remove duplicate Customer IDs while retaining the two Appeal columns.

Hence trying to move from:

to:

I could create two tables, tidy them up and then merge them back together again to achieve what I wanted but that seemed so inefficient.

[HufferD]

@KieftyKids ,

My apologies. I should’ve read that more carefully!

Group by Customer ID and add a new column called “All Data” using the “All Rows” operation:

Then add two custom columns:

For Appeal A:

List.Max(List.RemoveItems(Table.Column([All Data], "Appeal A"), null, ""))

For Appeal B:

List.Max(List.RemoveItems(Table.Column([All Data], "Appeal B"), null, ""))

Then delete the All Rows column.

Here’s the query:

let
    Source = Csv.Document(File.Contents("REPLACE-WITH-YOUR-PATH\Appeals.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Customer ID"}, {{"All Rows", each _, type table [Customer ID=nullable text, Appeal A=nullable text, Appeal B=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "A", each List.Max(List.RemoveNulls(Table.Column([All Rows], "Appeal A")))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B", each List.Max(List.RemoveNulls(Table.Column([All Rows], "Appeal B")))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All Rows"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Customer ID", Order.Ascending}})
in
    #"Sorted Rows"

Be sure to change the “REPLACE-WITH-YOUR-PATH” back to your path…

and the result:

[HufferD]

@KieftyKids did that do what you hoped?

[SoftwareTrain]

I solved this challenge with 3 methods and I think the easiest one is the third one as below:

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

Appeal Sample.pbix (40.5 KB)

[AlejandroSimon]

Hola, tengo este código para resolver la situación que menciona KeiftyKids. No puedo enviar la respuesta en la página original.

Espero que algo así sea lo que están buscando.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Customer ID"}, {{"All", each 
    let
        a = List.Skip(Table.ToColumns(_)),
        b = Table.FromColumns(List.Transform(a, (x)=> List.RemoveNulls(x)), List.Skip(Table.ColumnNames(_)))
    in b}}),
    Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
    Sol

Customer ID	Appeal A	Appeal B
339234	Appeal A Email Only
425407	Appeal A Email Only
433630	Appeal A Email Only	Appeal B Email Only
424796	Appeal A Email Only	Appeal B Email Only
433811	Appeal A Email Only	Appeal B Email Only
339544	Appeal A Email Only	Appeal B Email Only
425411	Appeal A Email Only	Appeal B Email Only
433631	Appeal A Email Only	Appeal B Email Only
424836	Appeal A Email Only	Appeal B Email Only
433897	Appeal A Email Only	Appeal B Email Only
352244	Appeal A Email Only
425414	Appeal A Email Only	Appeal B Email Only
433633	Appeal A Email Only	Appeal B Email Only
424837	Appeal A Email Only	Appeal B Email Only
433921	Appeal A Email Only	Appeal B Email Only
352290	Appeal A Email Only	Appeal B Email Only
425536	Appeal A Email Only	Appeal B Email Only
433797	Appeal A Email Only	Appeal B Email Only
424984	Appeal A Email Only	Appeal B Email Only
434018		Appeal B Email Only
352299	Appeal A Email Only	Appeal B Email Only
425542	Appeal A Email Only	Appeal B Email Only
433800	Appeal A Email Only	Appeal B Email Only
425034	Appeal A Email Only	Appeal B Email Only
434059		Appeal B Email Only

[KieftyKids]

Hi everyone

Thank you very much for your feedback. It’s been a great learning experience.