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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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"