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.

KyawMyoTun's avatar
KyawMyoTun
Helper IV
5 years ago

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's avatar
Smauro
Solution Sage
5 years ago

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's avatar
    KyawMyoTun
    Helper IV
    5 years ago

    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.