Table with 2 keys in not favourable format to connect with other two tables simultaneously

Power BI Data Model
1

Hi,

I have to design data model to prove a value of my analysis and pass to our data warehouse team later but I am stuck.

I have 3 files DUI ( with 2 keys: duty number and board number in several places) , BUI ( with link to board number from in one column ) and RUI ( with link to duty number in one column)

I need to connect DUI( activity code ) with RUI ( Rota Code) and DUI ( board code in several places) with BUI ( Board Code)

image
image1118Ă—358 130 KB

All files were received in plain text (txt) and I have managed to transform BUI and RUI into CSV file but I have no idea what to do with DUI:

image
image703Ă—515 19 KB

I have to mention , that I have to perform a few calculations for all these duties like : elapsed time, breaks between each parts of that duty ( L1,L2,L3,L4) and Time on Duty .

Files attached:
BUI - sample.csv (220.3 KB)
RUI- sample.csv (91.3 KB)
sample - dui .txt (886 Bytes)

Would be so grateful for guidance and directions for this .

Iwona

1 Like
2

Hi @Iwona,

So maybe you should consider another table shape all together. Explore something like this. It will repeat the DUI fields across rows and create a single row for each BUI

let
    DUIcols = List.Transform( { 1..List.Max( List.Transform( GroupCheck[DUI], each Text.Length( Text.Select( _, "," )) ) )+1 }, each "Column" &  Text.From( _ ) ),
    LastVal = Number.From( Text.Select( List.LastN( DUIcols, 1 ){0}, {"0".."9"})),
    BUIcols = List.Transform( { LastVal+1..LastVal+1 + List.Max( List.Transform( ExpandBUI[BUI], each Text.Length( Text.Select( _, "," )))) }, each "Column" &  Text.From( _ ) ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJNa8MwDIb/S85ClSzbca5bYR2UDNptMEKPPfew/w+TnC877Q6DBUfYiV/r1SMPQ7MHISDWwLTjdufIEQTo3+CpJ7af+49xqk9/s/F1/a7e/tZcYGiOwPAOp5OIB4roxWIQ/QLskHmexLz7EzQjBBTJy9GG41DaYN/+yYi+lRPS48yJy05cNsDIHbweD+ezzQNPAjcJEhAhaWpBb45fTrovze5T7b5D75eliIAeSMUH5YCOqvrcv9ZHmTEli2pdLVJn4Mf6ciWb+ljzGwqL7cxBkOIDjUxJVOPRnGtcNHHNw60yLbAkDLHCok0PWyxhxSJeuMTyfODfsYwXrhwVki5xbrYiaQ2MIWlR3NTKeyRZkFtOyYqc9nVr+++RrBq9Qm65JqJ1P9D4rcajzPsUHcV8tZQhFwx1KRVDyQdefgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    FilterRows = Table.SelectRows(Source, each not Text.StartsWith([Column1], "V")),
    AddIndex = Table.AddIndexColumn(FilterRows, "Index", 1, 1, Int64.Type),
    AddCheck = Table.AddColumn(AddIndex, "Check", each if Text.StartsWith([Column1],"D") then [Index] else null),
    FillDownCheck = Table.FillDown(AddCheck,{"Check"}),
    GroupCheck = Table.Group(FillDownCheck, {"Check"}, {{"DUI", each Text.Combine( {[Column1]{0}}, ",") , type nullable text}, {"BUI", each List.Skip( [Column1], 1 ) }})[[DUI], [BUI]],
    SplitDuiCol = Table.SplitColumn(GroupCheck, "DUI", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), DUIcols),
    ExpandBUI = Table.ExpandListColumn(SplitDuiCol, "BUI"),
    SplitBuiCol = Table.SplitColumn(ExpandBUI, "BUI", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), BUIcols )
in
    SplitBuiCol

Sample file
eDNA - Iwona Table with 2 keys.pbix (12.7 KB)

I hope this is helpful

2 Likes
3

Hi @Melissa

Could I please ask for PBI file if it is ok.

Many thanks, Iwona

1 Like
4

Hi @Iwona,

Attached the PBIX in my previous response

1 Like
5

Hi @Melissa ,

I was thinking I should keep every duty in one row in order to make calculations for them like : Elapsed time , breaks between the parts , and Time on duty ( Elapsed time - all breaks ) .
Am I able to perform these calculations based on your proposed table shape ?

Many thanks, Iwona

1 Like
6

@Iwona,

Shouldn’t be a problem…
Just show me how those values need to be calculated in the attached xlsx. Use cell references and/or color codes, whatever works for you and make sure to also include the expected outcome.

example calc logic.xlsx (11.2 KB)

1 Like
7

Hi @Melissa ,

Could you please let me know if my logic makes sense to you ?
example calc logic.xlsx (15.7 KB)

Many thanks,

Iwona

1 Like
8

Hi @Iwona,

Had a quick look, you’ve included the expected outcome for “Elapsed time for duties” but not for “Time on duty” OR “Breaks” if I’m not mistaken. Would be helpful if you could add those as well for validation.

1 Like
9

Hi @Melissa

I enrich this document about all calculations and comments .
example calc logic.xlsx (16.6 KB)

Many thanks in advance for your masterpiece .

Iwona

10

Hi @Iwona! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

11

@EnterpriseDNA,
Haven’t had the time to look into this yet for @Iwona but I will do so and report back. Please leave it open a little longer, thank you.

1 Like
12

Hi @Iwona,

Sorry I couldn’t get around to this sooner but here it goes…

Grouped the data on Duty code

image
image742Ă—292 9.13 KB

.
Added a record in which I applied your custom logic and expanded that.

image
image960Ă—261 10.6 KB

.
Performed the last two calculations over the nested tables

image

image
.
Did some final clean up and expanded everything.

Here’s your sample file.
eDNA - Iwona Nested Table transformations.pbix (13.7 KB)

I hope this is helpful

2 Likes
13

Thanks for your help @Melissa!

Hi @Iwona, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

14

Hi @Iwona, we’ve noticed that no response has been received from you since April 26th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

15

Hi @Iwona due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!