Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hello, I need help to model my data in PBI desktop.
below, is my raw data:
code | date time | description | category |
1 | 22/05/2018 08:22:40 | a | c |
1 | 22/05/2018 18:11:50 | b | d |
2 | 23/05/2018 07:34:42 | aa | dd |
2 | 23/05/2018 17:52:02 | bb | ee |
2 | 23/05/2018 22:02:12 | cc | ff |
3 | 24/05/2018 07:44:36 | aaa | bbb |
3 | 25/05/2018 07:46:01 | aaaa | bbbb |
now, this is the data I need:
code | date time | description | category | date time | description | category |
1 | 22/05/2018 08:22:40 | a | c | 22/05/2018 18:11:50 | b | d |
2 | 23/05/2018 07:34:42 | aa | dd | 23/05/2018 17:52:02 | bb | ee |
2 | 23/05/2018 17:52:02 | bb | ee | 23/05/2018 22:02:12 | cc | ff |
3 | 24/05/2018 07:44:36 | aaa | bbb | |||
3 | 25/05/2018 07:46:01 | aaaa | bbbb |
Please, see the conceptual rules:
1- for each day, it needs to get the codes and make the second entry become the destination.
2-code number 2, there are 3 entries in the same day, so it became 2 rows. the middle row is doubled, one stays in departure and the other goes one row above as destination.
3-code number 3, there is only one entry for each day, so its new columns will be empty.
How can I achieve this new table?
if I need to separate into 3 tables, because I have 3 rules, no problem. I can merge them later.
Solved! Go to Solution.
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"
HI @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.
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"
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"
how do I attach my pbix file here?
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |