Historical data with many to many relationship with multiple ids

[vdm]

Hi to everyone! This is my first post so thanks in advance for any help 🙂
We're tracking truck load and discharge by day in the following scenario:

We have 3 data sources, in different sharepoint folders.
1. daily loads, with date, hour, and truck id (dailyrecord_dd_mm_yy.xls)
2. another file has the truck loads capacity (since this is meassured again only if it's necessary and not by day), with truck id and load assigned. (trucks_dd_mm_yy.xls)
3. daily shift with operator, date and night/day shift. (shifts_dd_mm_yy.xls)

Since the files are exported from an application each day, a new file is generated (dailyrecord and shifts), and we can't modify these files, we´re managing the updates by making a query in Power Query that combines all files in each folder to get all the dailyrecords together and with the shifts too.

We're doing the same with the truck files (combining by folder), but this one will be updated in an x amount of time when a truck loads chages, or a truck is added or deleted. This is for the report to be automatically udpated so we don't have to generate a new report.

We're relating files by the truck id (dailyrecord with trucks). Since the truck files table will have repeated ids when the query fetches a second file uploaded, this generates a many to many relationship.

To have the "last" information about the load capacity for the dailyrecords, we've managed to create a calculated table to retrieve the last values by id. So the daily records have the updated load assigned and shown. This is okay.

The issue is that with this method we can't keep an historical track of what the loads where before this file (trucks) gets updated, in the previous dailyrecords.

We've tried merge queries (inner left to keep the dailyrecords), but it duplicates the records because of the duplicates ids in the truck files.

Is there a way to "harcode" the load in the dailyrecord rows so this value doesn't change?

Since the trucks table doesn´t have dates we are not being able to get a lookup or filter option to match the dailyrecords.

Maybe there's an approach we're not seeing, so any help is appreciated.

Please excuse my english and thanks in advice

Kind regards.

[vdm]

Hi @v-heq-msft, thanks a lot for your answer!
I'm wasn't able to find how to create a static value column so store the hardcoded load.

Could you give me a hint? maybe i'm not searching in google the right way.

Thanks again.

Kind regards.

[v-heq-msft]

Hi @vdm ,
Thank you for your question. Hardcoding here means storing the load capacity as a column of static values in the data model, i.e., implementing the history record keeping that. Here’s how you can do it:
1.Open the Power Query Editor by selecting “Transform Data” in the Home tab of the ribbon.
2.Select your table and choose "Advanced Editor".
3.Use M code to create a new column

let
    Source = <Your source>
    #"Add column" = Table.AddColumn(Source, "New Column Name", each<Your hardcode value>)
in
    #"Add column"

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-heq-msft · Accepted solution]

Hi @vdm ,
According to your description, here are some ways you can go about trying to manage historical data and resolve many-to-many relationships:
To go about saving historical data, you can import the daily records, truck capacities, and shifts from the SharePoint folders into Power Query. Use the 'Append Queries' feature to combine all daily records into a single query. Since your truck capacities file can have multiple records for the same truck ID over time, you need to create a unique identifier. You can do this by adding a column that concatenates the truck ID with a timestamp or version number when the capacity is measured. Use the 'Merge Queries' feature to join the daily records with the historical truck capacities. Use the truck ID as the key for the merge.
After merging, expand the merged column to include the load capacity.
Hardcode load values：
To hardcode the load values into your daily records, after the merge, add a custom column that stores the load capacity as a static value. This value will then persist even if the truck capacities file is updated.

To manage the relationship, you can use a calculated table to establish a relationship with the daily records.

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly