Forum Discussion

KyawMyoTun's avatar
KyawMyoTun
Helper IV
5 years ago
Solved

Append columns based on column values

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 111...
  • Smauro's avatar
    Smauro
    5 years ago

    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.

Icey's avatar
Icey
Community Support
5 years ago

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.