Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PaulBoden
Helper I
Helper I

Updating rows based on earlier row

‎07-04-2024 03:57 AM

Hi all,

 

I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source.

 

The solution needs to:

  • Find instances where a flag has indicated a manual adjustment has been made to a user entry
  • Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable)
  • Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment.

 

Considering the attached table, I’ve tried to outline the logic I’m trying to implement below:

For the purposes of this explanation, I’ll refer to the manual adjustment as row X.

 

"IF Flag = “Adj” (

                IF Case = “L” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Output = “H”

                )

                IF Case = “M” (

                                For any Date >=  Than X.Date WHERE ID = X.ID And Case != X.Case

                                                Level = X.Level

                )

)"

 

Any advice on how to optimally achieve this within PowerBI would be greatly appreciated.

 

 

 

IDCaseFlagLevelDateOutput
1HCSource103/07/2024S
1HCSource104/07/2024S
1LAdj105/07/2024S
1HCSource106/07/2024S
1HCSource107/07/2024S
1LSource108/07/2024S
1HCSource109/07/2024S
1HCSource110/07/2024S
1HCSource111/07/2024S
1HCSource112/07/2024S
2HCSource303/07/2024S
2HCSource304/07/2024S
2HCSource305/07/2024S
2HCSource306/07/2024S
2HCSource307/07/2024S
2LSource308/07/2024S
3HCSource303/07/2024S
3HCSource304/07/2024S
3HCSource305/07/2024S
3HCSource306/07/2024S
3HCSource307/07/2024S
3LAdj308/07/2024S
3HCSource309/07/2024S
3HCSource310/07/2024S
3HCSource311/07/2024S
3HCSource312/07/2024S
4HCSource503/07/2024S
4HCSource504/07/2024S
4LAdj505/07/2024S
4HCSource505/07/2024S
4HCSource506/07/2024S
4HCSource507/07/2024S
4HCSource508/07/2024S
4HCSource509/07/2024S
4HCSource510/07/2024S
4HCSource511/07/2024S
4LSource512/07/2024S
5HCSource203/07/2024S
5MAdj304/07/2024S
5HCSource204/07/2024S
5HCSource205/07/2024S
5HCSource206/07/2024S
5HCSource207/07/2024S
5HCSource208/07/2024S
5MSource309/07/2024S
5HCSource310/07/2024S
5HCSource311/07/2024S
5HCSource312/07/2024S
5HCSource313/07/2024S
Labels:
Message 1 of 11
641 Views
0
10 REPLIES 10
dufoq3
Super User
Super User

‎07-08-2024 09:48 AM

@PaulBoden, could you provide expected result based on sample data?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 8 of 11
482 Views
0
PaulBoden
Helper I
Helper I
In response to dufoq3

‎07-08-2024 10:06 AM

"Character restriction prevents me from showing the full desired output but I've included a sample below for ID 4 and 5."

 

IDCaseFlagLevelDateOutput
4HCSource503/07/2024S
4HCSource504/07/2024S
4LAdj505/07/2024S
4HCSource505/07/2024H
4HCSource506/07/2024H
4HCSource507/07/2024H
4HCSource508/07/2024H
4HCSource509/07/2024H
4HCSource510/07/2024H
4HCSource511/07/2024H
4LSource512/07/2024S
5HCSource203/07/2024S
5MAdj304/07/2024S
5HCSource304/07/2024S
5HCSource305/07/2024S
5HCSource306/07/2024S
5HCSource307/07/2024S
5HCSource308/07/2024S
5MSource309/07/2024S
5HCSource310/07/2024S
5HCSource311/07/2024S
5HCSource312/07/2024S
5HCSource313/07/2024S
Message 9 of 11
474 Views
0
PaulBoden
Helper I
Helper I
In response to PaulBoden

‎07-09-2024 02:47 AM

@AlienSx @dufoq3  Apologies for pestering - I appreciate all the support so far and I'm very close to a working solution. The outputs look good but I'm getting a number of false positives as well.

 

Are you able to advise me on how I can update the proposed logic to specifically target records following a manual adjustement. The logic is intended to achieve the below:

 

If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L"".

 

The second part of the logic ([Case] != "L") is because eventually the golden source will update and a record will be ingested containing x.[ID], x.[Case] and [Flag] = Source.

 

Any advice would be greatly appreciated

Message 10 of 11
450 Views
0
dufoq3
Super User
Super User
In response to PaulBoden

‎07-09-2024 08:15 AM

Hi @PaulBoden, for ID 4 check this:

 

You've mentioned:

If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L""., but in your sample output there is also H as output for row nubmer 4 where the date equals [Flag] = "Adj" date. You cand edit this in GroupedRows step if necessary.

 

Regarding ID 5, you have to explan logic for which rows you want to change Level.

 

Result

dufoq3_0-1720537916168.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldOxCsIwFEbhd8lcaJs0VkdxcdDJsXRSFxdB8P1thQ7puT9eB0X048rtSYYhtKEKx8P0dnm+X9f79GH+pkl109exid38SxgrCTsTnqbX/vZYUPZO23hhL/+2dFvvwJ0Tto0Xtl4YASNgsqMIyCgCMoyADCMgw8R1mGSHSd6lBeTSAnJpAbm0gFw6FZfgv415FG1oHEUBeRQF5FHsALNdRUBW6YpHk+0iYpobMp2ATCcg8wnIfDY08gnIfMszLB3rZQyMdr0ZnsvzynJimhuynoCsJyDrCch6y8a/7h4HirsnIOMJ6Kn3hat64wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Case = _t, Flag = _t, Level = _t, Date = _t, Output = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Level", Int64.Type}, {"Date", type date}}, "sk-SK"),
    Filtered_ID_4_Only = Table.SelectRows(ChangedType, each ([ID] = 4)),
    GroupedRows = Table.Group(Filtered_ID_4_Only, {"ID"}, {{"All", each _, type table}, {"Transformed", each 
        [ a = Table.SelectRows(_, (x)=> x[Flag] = "Adj" and x[Case] = "L")[Date]{0}?,
          b = Table.AddColumn(_, "New Output", (x)=> try if x[Date] >= a and x[Case] <> "L" then "H" else x[Output] otherwise x[Output], type text)
        ][b], type table }}),
    CombinedTransformed = Table.Combine(GroupedRows[Transformed])
in
    CombinedTransformed

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 11 of 11
444 Views
0
AlienSx
Super User
Super User

‎07-04-2024 07:37 AM 
let
    Source = Table.Sort(your_table, {"ID", "Date"}),
    rec = (x) => [L = {"Output", each "H"}, M = {"Level", each x{0}[Level]}],
    fx = (tbl) => Table.TransformColumns(tbl, Record.FieldOrDefault(rec(tbl), tbl{0}[Case], {})),
    group = Table.Group(
        Source, 
        {"ID", "Case"},
        {"x", fx},
        GroupKind.Local, 
        (s, c) => Number.From(s[ID] <> c[ID] or List.Contains({"L", "M"}, c[Case]))
    ),
    z = Table.Combine(group[x])
in
    z
Message 3 of 11
588 Views
0
PaulBoden
Helper I
Helper I
In response to AlienSx

‎07-05-2024 04:27 AM

Thanks for your comment.

 

I suspect this probably close to the optimal approach but I'm struggling to implement it correctly. Currently I'm seeing the following error:

 

Expression.Error: A cyclic reference was encountered during evaluation.

 

Could you please expand on what the above is doing and why so I can identify the problem?

 

Thanks in advance for the help. 

Message 4 of 11
554 Views
0
AlienSx
Super User
Super User
In response to PaulBoden

‎07-05-2024 06:09 AM

create blank query, replace it's content with my code and change your_table in my code to the name of your table. Looks like you are trying to add new (conditional) column - don't do that. Watch this. 

Message 5 of 11
544 Views
PaulBoden
Helper I
Helper I
In response to AlienSx

‎07-08-2024 09:18 AM

Hi - The outputs not quite I was expecting. I can't see any reference in your code to 'Flag' so I was wondering this this could be because the transformation is being applied to all records, regardless of whether they are from source or a manual adjustment (adj)?

 

Also, I was wondering if this could be achieved as part of a transformation step within an existing table? I ask because the source table referenced is quite large and already requires several steps to be applied - I wonder if it would be more performative to execute the above within the original query rather than as a stand-alone?

 

If you believe this is a viable approach, any advice on how I would modify the query to integrate it correctly would be greatly appreciated! Thanks

Message 7 of 11
494 Views
0
PaulBoden
Helper I
Helper I
In response to AlienSx

‎07-05-2024 07:59 AM

Yes - I subsequently realised thanks. I was attempting to execute as a transformation within the existing table. Thanks again. 

 

Will confirm once I've got it working.

Message 6 of 11
538 Views
0
bhanu_gautam
Super User
Super User

‎07-04-2024 04:23 AM

@PaulBoden , We can try to create a new calculated column based on logic provided by you

 

AdjustmentOutput =
VAR CurrentID = 'YourTable'[ID]
VAR CurrentDate = 'YourTable'[Date]
VAR CurrentCase = 'YourTable'[Case]
VAR CurrentFlag = 'YourTable'[Flag]
VAR CurrentLevel = 'YourTable'[Level]

RETURN
IF (
CurrentFlag = "Adj",
IF (
CurrentCase = "L",
"H",
IF (
CurrentCase = "M",
CurrentLevel,
BLANK()
)
),
IF (
'YourTable'[Date] >= CALCULATE(MIN('YourTable'[Date]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "L")),
"H",
IF (
'YourTable'[Date] >= CALCULATE(MIN('YourTable'[Date]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "M")),
CALCULATE(MAX('YourTable'[Level]), FILTER('YourTable', 'YourTable'[ID] = CurrentID && 'YourTable'[Flag] = "Adj" && 'YourTable'[Case] = "M")),
'YourTable'[Output]
)
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 11
623 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All