Forum Discussion
Append and shape data
Hi there,
My requirements are fairly complex and am hoping someone might be able to help out.
At a high level, I want to plot user activity on a map.
- Users can either register or transact.
- The geocordinate of registrations are recorded in a table 'Tracker'
- The table 'Tracker' contains other events, but I am not interested in them
- Transactions are recorded in a table called 'Transactions
- Transactions should be bucketed into two buckets: First Transactions and Subsequent
- Transactions can happen anywhere, including in Locations which are in the database
- A table 'Locations' stores details of locations including geocoordinates
I would like to combine all this data into one table with the following columns:
- Table: a record of where the entry came from (which table)
- Created_on: common field accross the tables but excluding entries from the 'Locations' table
- Created_on_Location: Entries from the Created_on field of the Locations table
- Event Type: Either Register/Location/1st transaction/Transaction [Ignores non 'Register' events from the 'Tracker' table]
- Lat
- Long
- UserID
- LocationID
I appreciate that this is a really really long shot but I am doing this transformation in XL and it would be amazing to be able to do this in PBI. I have created a PBI file with all the tables, including the desired outcome, with sample data in it. I am trying to figure out how to attach the file to this post. In the meanwhile, here is a link to it: 'http://cafepixel.me/attach/AppendingData.pbix The data model described above is shown here:
It seems that you're looking for a UNION calculated table. What is the logic to determine 1st transaction and so on.
union table = UNION ( SELECTCOLUMNS ( Locations, "Table", "Location", "Created on", "", "Created on_Location", Locations[Created On ], "event type", "event type", "lat", Locations[Lat], "long", Locations[Long], "userid", "", "locationid", Locations[Id] ), SELECTCOLUMNS ( Transactions, "Table", "Transactions", "Created on", Transactions[Created On ], "Created on_Location", "", "event type", "event type", "lat", Transactions[Lat], "long", Transactions[Long], "userid", RELATED ( Users[Id] ), "locationid", RELATED ( Locations[Id] ) ), SELECTCOLUMNS ( FILTER ( Tracker, Tracker[Event type] = "Register" ), "Table", "Register", "Created on", Tracker[Created On ], "Created on_Location", "", "event type", Tracker[Event type], "lat", Tracker[Lat], "long", Tracker[Long], "userid", RELATED ( Users[Id] ), "locationid", "" ) )
2 Replies
Not sure you want or need a single table. (sorry don't look at attachments...just general db feedback)
Ideally your User table should be Distinct - with no user repeating....and that should remain stand alone.
If there is NOT a 1:1 between Tracker/User or NOT a 1:1 between Tracker/Transaction - then I'm not sure it would make sense to attempt to merge them into a single table.
Presuming there is a 1:1 between Location/Transaction - a Merge Query to put them together eliminates a table to simplify things a little.
Beyond that your join lines give you a virtual single table in a sense as you can relate things together. A merge of multiple tables that do not have a 1:1 will result in alot of duplicate records and some confusing data. The duplicates can be removed but if, for example, tracker fields don't directly relate to transaction fields - then they shouldn't be in the same record of the same table.
It seems that you're looking for a UNION calculated table. What is the logic to determine 1st transaction and so on.
union table = UNION ( SELECTCOLUMNS ( Locations, "Table", "Location", "Created on", "", "Created on_Location", Locations[Created On ], "event type", "event type", "lat", Locations[Lat], "long", Locations[Long], "userid", "", "locationid", Locations[Id] ), SELECTCOLUMNS ( Transactions, "Table", "Transactions", "Created on", Transactions[Created On ], "Created on_Location", "", "event type", "event type", "lat", Transactions[Lat], "long", Transactions[Long], "userid", RELATED ( Users[Id] ), "locationid", RELATED ( Locations[Id] ) ), SELECTCOLUMNS ( FILTER ( Tracker, Tracker[Event type] = "Register" ), "Table", "Register", "Created on", Tracker[Created On ], "Created on_Location", "", "event type", Tracker[Event type], "lat", Tracker[Lat], "long", Tracker[Long], "userid", RELATED ( Users[Id] ), "locationid", "" ) )
