Append columns based on column values

[KyawMyoTun]

Dear Experts,

     I'd to request you a help to solve my issue.

Date	Ticket ID	Passenger Name	Bus Name
01/01/2021	1111	David	Lion
01/01/2021	1111	Sam	Lion
01/02/2021	1111	David	Tiger
01/02/2021	1111	Sam	Tiger
01/02/2021	1112	Sue	Tiger
01/02/2021	1113	Adam	Lion
01/02/2021	1114	John	Lion

Firstly David and Same bought the bus tickets for bus name "Lion".
Later on, they changed to take the bus name "tiger".
Because they bought the tickets at once, the Ticket ID is the same with different names.
So, the bus ticket sold for 1st Jan 2021 is 2 tickets but with the Bus name"Lion".
I'd like to get the 2 tickets with Bus name "Tiger" for 1st Jan 2021.

My desire result is,

Date                 No. of Tickets        Bus Name          Passenger Name
1st Jan 2021     2 tickets                   Tiger                David, Sam
2nd Jan 2021    3 tickets                   Tiger,Lion        Sue, Adam, John

Best Regards,
KMT

[Icey]

Hi @KyawMyoTun ,

Try to create columns and measures like so:

Date Format Column = 
VAR Day_ =
    DAY ( [Date] )
VAR LastNumofDay =
    VALUE ( RIGHT ( Day_, 1 ) )
VAR Day_Format =
    Day_ & SWITCH ( LastNumofDay, 1, "st", 2, "nd", 3, "rd", "th" )
VAR Month_Format =
    FORMAT ( [Date], "mmm" )
VAR Year_Format =
    FORMAT ( [Date], "yyyy" )
RETURN
    Day_Format & " " & Month_Format & " " & Year_Format

No. of Tickets Measure = 
VAR TicketIds =
    VALUES ( 'Table'[Ticket ID] )
VAR TicketIds_ =
    CALCULATETABLE (
        VALUES ( 'Table'[Ticket ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
VAR t =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Ticket ID]
            IN TicketIds
                && NOT ( 'Table'[Ticket ID] IN TicketIds_ )
                    && 'Table'[Date] = MAX ( 'Table'[Date] )
    )
RETURN
    COUNTAX ( t, [Ticket ID] )

Bus Name Measure = 
VAR TicketIds =
    VALUES ( 'Table'[Ticket ID] )
VAR TicketIds_ =
    CALCULATETABLE (
        VALUES ( 'Table'[Ticket ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
VAR t =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Ticket ID]
            IN TicketIds
                && NOT ( 'Table'[Ticket ID] IN TicketIds_ )
                    && 'Table'[Date] = MAX ( 'Table'[Date] )
    )
RETURN
    CONCATENATEX(SUMMARIZE(t,[Bus Name]),[Bus Name],", "
)

Passenger Name Measure = 
VAR TicketIds =
    VALUES ( 'Table'[Ticket ID] )
VAR TicketIds_ =
    CALCULATETABLE (
        VALUES ( 'Table'[Ticket ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
VAR t =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Ticket ID]
            IN TicketIds
                && NOT ( 'Table'[Ticket ID] IN TicketIds_ )
                    && 'Table'[Date] = MAX ( 'Table'[Date] )
    )
RETURN
    CONCATENATEX ( SUMMARIZE ( t, [Passenger Name] ), [Passenger Name], ", " )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

try this.

you should just copy and paste the attached code into the advanced editor

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQyAAUi6JZZkpQNonMz9PKVYHu6LgxFx0JUY4zAnJTE8twqkKYhBuNUYgNaWpeNUYAynHFPwuMgFSXvkZeXA1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ticket ID" = _t, #"Passenger Name" = _t, #"Bus Name" = _t]),
    #"Raggruppate righe" = Table.Group(Origine, { "Ticket ID"}, {{"Date", each _[Date]{0}}, {"name", each Text.Combine(List.Distinct(_[Passenger Name]),",")}, {"bus name", each List.Last(_[Bus Name])}}),
    #"Raggruppate righe1" = Table.Group(#"Raggruppate righe", {"Date"}, {{"Passenger Name", each Text.Combine(_[name],",")}, {"Bus Name", each Text.Combine(List.Distinct(_[bus name]),",")}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Raggruppate righe1", "Nr of Tickets", each List.Count(Text.Split([Passenger Name],",")))
in
    #"Aggiunta colonna personalizzata"

[Smauro · Accepted solution]

Hi @KyawMyoTun ,

You could try this, which first finds the first appearance of the ticket and its last change, then formats the data into your required format:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQyAAUi6JZZkpQNonMz9PKVYHu6LgxFx0JUY4zAnJTE8twqkKYhBuNUYgNaWpeNUYAynHFPwuMgFSXvkZeXA1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ticket ID" = _t, #"Passenger Name" = _t, #"Bus Name" = _t]),
    PreviousStep = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    
    #"Fix tickets" = Table.Group(PreviousStep, {"Ticket ID", "Passenger Name"}, {{"Date", each List.Min([Date]), type nullable date}, {"Bus Name", each Table.Sort(_[[Date],[#"Bus Name"]],{{"Date", Order.Descending}}){0}[#"Bus Name"], type text}}),
    #"Group per Date" = Table.Group(#"Fix tickets", {"Date"}, {{"No. of Tickets", each let n = List.Count([Ticket ID]) in Text.From(n) & (if n < 2 then " Ticket" else " Tickets"), type text}, {"Bus Name", each Text.Combine(List.Distinct([Bus Name]), ", "), type text}, {"Passenger Name", each Text.Combine(List.Distinct([Passenger Name]), ", "), type text}})
in
    #"Group per Date"

You can test this with your data replacing PreviousStep with your last step's name.

[KyawMyoTun]

Hi @Smauro,

I am not so familiar with Power Query and I couldn't insert my source destionation.
Here is my soure path and please help how can I proceed.
"D:\KMT\PB\Travel Dashboard test\Remove duplicates by date\Bus.xlsx"

Best Regards,
KMT

[Smauro]

Hi @KyawMyoTun 

I will assume you have the table already loaded in Power BI. If you go on "Transform Data", the query editor will open. There, you'll have to use "Advanced Editor".
The "Advanced Editor" window should look like this:


Here, every new line is a new step (usually). Thus, by "PreviousStep" I mean #"Added Custom". So, you will need to change the part after this step:

in
   #"Added Custom"

to

    ,
    #"Fix tickets" = Table.Group(#"Added Custom", {"Ticket ID", "Passenger Name"}, {{"Date", each List.Min([Date]), type nullable date}, {"Bus Name", each Table.Sort(_[[Date],[#"Bus Name"]],{{"Date", Order.Descending}}){0}[#"Bus Name"], type text}}),
    #"Group per Date" = Table.Group(#"Fix tickets", {"Date"}, {{"No. of Tickets", each let n = List.Count([Ticket ID]) in Text.From(n) & (if n < 2 then " Ticket" else " Tickets"), type text}, {"Bus Name", each Text.Combine(List.Distinct([Bus Name]), ", "), type text}, {"Passenger Name", each Text.Combine(List.Distinct([Passenger Name]), ", "), type text}})
in
    #"Group per Date"

Take note that I've changed PreviousStep  to #"Added Custom".

Best,

Spyros

[KyawMyoTun]

Dear @Smauro ,

   Thanks a lot for your help.
I have just import your code and it works properlly now.
Actually I was wrong to express my desire result.
I want to use the "Bus Name" and "Passenger Name" as slicer like below.
The Bus Name should only be last changed bus name (e.g "Tiger" for Ticket ID 1111)
Could you please help me to split out by respecitve data.

[AlB]

Hi @KyawMyoTun 

Thi swould be simpler in DAX but here is a possible PQ solution. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNDJR0lQyAAUi6JZZkpQNonMz9PKVYHu6LgxFx0JUY4zAnJTE8twqkKYhBuNUYgNaWpeNUYAynHFPwuMgFSXvkZeXA1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ticket ID" = _t, #"Passenger Name" = _t, #"Bus Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Ticket ID", Int64.Type}, {"Passenger Name", type text}, {"Bus Name", type text}}),
    LastAppearances_ = Table.SelectRows(#"Changed Type", each [Date] = List.Max(Table.SelectRows(#"Changed Type", (inner)=> inner[Ticket ID]=[Ticket ID])[Date])),

    #"Grouped" = Table.Group(#"Changed Type", {"Date"}, {{"Aux", each _}}),
    #"Added Custom" = Table.AddColumn(Grouped, "Custom", each Table.NestedJoin([Aux], {"Ticket ID", "Passenger Name"}, LastAppearances_, {"Ticket ID", "Passenger Name"}, "Res", JoinKind.LeftOuter)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Res"}, {"Res"}),
    #"Expanded Res" = Table.ExpandTableColumn(#"Expanded Custom", "Res", {"Date", "Ticket ID", "Passenger Name", "Bus Name"}, {"Date.1", "Ticket ID", "Passenger Name", "Bus Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Res",{"Aux"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date.1", "Ticket ID", "Passenger Name", "Bus Name"}, {{"Check
", each List.Min([Date]), type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Date.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Check#(cr)#(lf)", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Ticket ID", "Passenger Name", "Bus Name"}),
    #"Grouped Rows1" = Table.Group(#"Reordered Columns", {"Date"}, {{"Number of tickets", each Table.RowCount(_), Int64.Type}, {"Bus name", each Text.Combine(List.Distinct([Bus Name]), ", "), type text}, {"Passenger names", each Text.Combine(List.Distinct([Passenger Name]), ", "), type text}})
in
    #"Grouped Rows1"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[KyawMyoTun]

Hi @AIB,

   I am not so familiar with Power Query and I couldn't insert my source destionation.
Here is my soure path and please help how can I proceed.
"D:\KMT\PB\Travel Dashboard test\Remove duplicates by date\Bus.xlsx"

Please help me to proceed also by DAX.

Best Regards,
KMT