It’s data from carnival where users can find locations and dates of the events.
I need to extract data from column “Action” to get “Region Name”, “Objectives”, “Selected Date”. I had an idea to pivot column Category and Action, but it didn’t work well. I believe it because logs ID is correlated with time of interaction, not with user interaction. Is it a way to do it with M language? EventData_br.csv (7.1 KB).
Thank you in advance for any help!
Interesting case. I think there will be far more sophisticated ways to solve this, but I did the following: First I merged the Category and Action columns with a standing out delimiter like #. Then I added Conditional Columns based on the categrory that the merged column contains. I did that for the Region Names and Objectives. After that I extracted the text after # in those columns. For extracting the date I copied the original Action column and changed the column type to date so that all the not Dates went on error. After that I have replaced the errors by null.
I have added a PBIX file so you can see the querysteps.
You’ll have to play with the conditions in de querystep of the Conditional column. The conditional column for the Objectives now contains the condition if column ‘Merged’ contains “Objetivo#” but if you delete the “#” it will extract the rows ID 177, 189, 148 too. After that you’ll have some results like “Objetivo - Beber” and “Beber” in the same column, but I am sure you can fix this.
