Append columns based on column values

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"

 

 

 

 

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 

 

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.