Forum Discussion
create destination from single departure list
thanks a lot for your help Phil_Seamark!
I managed to get the final table that I need.
I started with your final table, then I duplicated the full query into another new table.After that, I made 2 filters, one on each table, and then I appended them together and removed duplicate rows.
One filter is to remove blank dates from one table. The other filter is to remove duplicates from the new column code+date (only date without time).
Bad thing is, I doubled the data to perform the 2 filters. but I got what I wanted!
Table1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LDoAgDATQqxjWJtACQnoV44Lv/Y9gi0ZNZNEupi/TfVegVoWojddoIC6REDlJPEUd6+8OkUCizFMHEI72AYGskwJpqFMBgbxEWTpamxFEMhKVwqv3QawQ9/njrj9pVOXX+K/ZLnMjVscJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [code = _t, #"date time" = _t, description = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"date time", type datetime}, {"description", type text}, {"category", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date time", Order.Ascending}}), #"Inserted Date" = Table.AddColumn(#"Sorted Rows", "Date", each DateTime.Date([date time]), type date), #"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Date", "Index.1", "code"},#"Added Index1",{"Date", "Index", "code"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"code", "date time", "description", "category", "Date", "Index", "Index.1"}, {"code.1", "date time.1", "description.1", "category.1", "Date.1", "Index.2", "Index.1.1"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Index1",{"code", "date time", "description", "category", "Date", "date time.1", "description.1", "category.1"}), #"Rows Filtered" = Table.SelectRows(#"Removed Other Columns", each ([date time.1] <> null)), #"Append queries" = Table.Combine({#"Rows Filtered", #"Table1 (2)"}), #"Remove column" = Table.RemoveColumns(#"Append queries",{"Date"}), #"Remove duplicates" = Table.Distinct(#"Remove column") in #"Remove duplicates"Table1 (2)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LDoAgDATQqxjWJtACQnoV44Lv/Y9gi0ZNZNEupi/TfVegVoWojddoIC6REDlJPEUd6+8OkUCizFMHEI72AYGskwJpqFMBgbxEWTpamxFEMhKVwqv3QawQ9/njrj9pVOXX+K/ZLnMjVscJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [code = _t, #"date time" = _t, description = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"date time", type datetime}, {"description", type text}, {"category", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date time", Order.Ascending}}), #"Inserted Date" = Table.AddColumn(#"Sorted Rows", "Date", each DateTime.Date([date time]), type date), #"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Date", "Index.1", "code"},#"Added Index1",{"Date", "Index", "code"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"code", "date time", "description", "category", "Date", "Index", "Index.1"}, {"code.1", "date time.1", "description.1", "category.1", "Date.1", "Index.2", "Index.1.1"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Index1",{"code", "date time", "description", "category", "Date", "date time.1", "description.1", "category.1"}), #"Added column" = Table.AddColumn(#"Removed Other Columns", "New", each Text.Combine({Text.From([code], "pt-BR"), "|", Text.From([Date], "pt-BR")}), type text), #"Remove duplicates" = Table.Distinct(#"Added column", {"New"}), #"Remove column" = Table.RemoveColumns(#"Remove duplicates",{"New"}) in #"Remove column"
Microsoft EmployeeHI Anonymous
I have this calculated table which gets close
Table =
VAR T1 = ADDCOLUMNS(
'Table1' ,
"Next Date Time" ,
VAR D =
MINX(
FILTER('Table1',
'Table1'[Code] = EARLIER('Table1'[code]) &&
'Table1'[date time] > EARLIER('Table1'[date time])
),'Table1'[date time])
RETURN IF(FORMAT(d,"YYYYMMDD") = FORMAT([date time],"YYYYMMDD"),D)
)
RETURN
GENERATEALL(
T1,
FILTER(
SELECTCOLUMNS(
'Table1',
"Next Code",[code],
"Next date time2",[Date Time],
"Next Description",[description] ,
"Next Cateogry",[category]
),
[Next Date Time] = [Next date time2] &&
[code]=[Next Code] &&
FORMAT([date time],"YYYYMMDD") = FORMAT([Next Date Time],"YYYYMMDD")
)
)The question I have, is what is the rule to get rid of the 2nd lines for code 1 and 2 (highlighted), but keep both lines for code 3?
thanks for your reply.
yes, it is really close to what I need. but it is a calculated table using DAX, and I need a script in M language.
I have extra transformations to perform after this first issue, but i can perform myself on some tests I already did.
maybe you could filter off the empty lines for codes 1 and 2, keeping line for code 3, if you use the comand EARLIER, and then compare the values. code 1 and 2 are repeated, code 3 is not. but once again, this will be a solution in DAX, and I need M language inside the query editor.
- Phil_Seamark
HI Anonymous
This M gets us to the same place as the DAX. So just need to determine the rule to apply that will remove the lines for codes 1 and 2 but keep the lines for code 3.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LDoAgDATQqxjWJtACQnoV44Lv/Y9gi0ZNZNEupi/TfVegVoWojddoIC6REDlJPEUd6+8OkUCizFMHEI72AYGskwJpqFMBgbxEWTpamxFEMhKVwqv3QawQ9/njrj9pVOXX+K/ZLnMjVscJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [code = _t, #"date time" = _t, description = _t, category = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"date time", type datetime}, {"description", type text}, {"category", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date time", Order.Ascending}}), #"Inserted Date" = Table.AddColumn(#"Sorted Rows", "Date", each DateTime.Date([date time]), type date), #"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Date", "Index.1", "code"},#"Added Index1",{"Date", "Index", "code"},"Added Index1",JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"code", "date time", "description", "category", "Date", "Index", "Index.1"}, {"code.1", "date time.1", "description.1", "category.1", "Date.1", "Index.2", "Index.1.1"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Index1",{"code", "date time", "description", "category", "date time.1", "description.1", "category.1"}) in #"Removed Other Columns"great! that is something I really can use! I will try to find that missing filter, and if I find I will return the full script here. once again, thanks!