Forum Discussion
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...
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.
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 @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
