Append and shape data

[duncanelliot]

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:

[Eric_Zhang · Accepted solution]

@duncanelliot

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", ""
    )
)

[CahabaData]

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.