topic Re: Calculate Iterate Levels in M in Data Engineering

Calculate Iterate Levels in M

Yaroslav__D — Thu, 20 Mar 2025 11:02:50 GMT

Hi ALL!
I tried to calculate in power query levels for my accounts to calculate all of them later .
I used this code to do it , but is`s don`t work , maybe someone know what could be wrong?

let
MappingRules = #"MappingRules",

    Level1 = Table.AddColumn(
        MappingRules,
        "Level",
        each if List.ContainsAll(Balance[FinalAccount], { [SourceAccount] }) then 1 else null,
        Int64.Type
    ),

CheckIfCompleted = List.Contains(Level1[Level], null),

    IterateLevels = List.Generate(
        () => [Table = Level1, LevelNum = 1],
        each CheckIfCompleted,
        each
            let
                CurrentTable = _[Table],
                NextLevelNum = _[LevelNum] + 1,

                UpdatedTable = Table.TransformColumns(
                    CurrentTable,
                    {"Level", each
                        if _ <> null then _
                        else if List.ContainsAll(CurrentTable[ComponentAccount],
                                                 Table.SelectRows(CurrentTable, each [Level] = _[LevelNum])[SourceAccount])
                             then NextLevelNum
                             else null,
                        Int64.Type}
                ),

                UpdatedCheck = List.Contains(UpdatedTable[Level], null)
            in
                [Table = UpdatedTable, LevelNum = NextLevelNum, CheckIfCompleted = UpdatedCheck],
        each _[Table]
    ),

FinalLevelsTable = List.Last(IterateLevels)
in
FinalLevelsTable

Re: Calculate Iterate Levels in M

lbendlin — Thu, 20 Mar 2025 23:16:03 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculate Iterate Levels in M

v-priyankata — Tue, 08 Apr 2025 11:10:07 GMT

Hi Yaroslav__D,

We are grateful for your participation in the Microsoft Fabric Community forum.

Did your issue get resolved? If not, as lbendlin suggested, "Please provide sample data so we can suggest a solution. We tried your query but got the error: 'Expression.Error: The name 'MappingRules' wasn't recognized.' Kindly provide more insights on this issue.

Thanks.

Re: Calculate Iterate Levels in M

v-priyankata — Mon, 14 Apr 2025 06:38:47 GMT

Hi Yaroslav__D

May I ask if you have resolved this issue? if not Please provide sample data so we can suggest a solution.

Thank you.

Re: Calculate Iterate Levels in M

Yaroslav__D — Mon, 14 Apr 2025 07:21:24 GMT

Hi , no, this issue not resolved .
yes , sure , mappingrules looks literally like this

TargetAccount	SourceAccount	Operator
P003099	P001010	+
P003099	P002010	+
P003099	P002030	+
P008999	P003099	+
P008999	P004050	+
P013099	P013010	+
P013099	P013020	+
P013099	P013025	+
P013099	P013030	+
P013099	P013035	+
P013099	P013040	+
P013099	P013050	+
P013099	P013062	+
P013099	P013064	+
P013099	P013070	+
P013099	P013080	+
P013099	P013095	+
P014099	P014010	+

in Balance we have similar list where we find our Source Account , to give level 1 or null

Thanks

Re: Calculate Iterate Levels in M

lbendlin — Sat, 19 Apr 2025 15:57:46 GMT

consider using Expression.Evaluate

Re: Calculate Iterate Levels in M

v-priyankata — Tue, 15 Jul 2025 04:46:42 GMT

Hi Yaroslav__D

Thank you for reaching out to the Microsoft Fabric Forum Community. sorry for delay.

Idea to use Expression.Evaluate is a really good one or Please try the code below. it may helps you. lbendlin Thanks for your inputs.
If the issue persists, feel free to reach out to us. We are happy to assist you.

Table.TransformRows(
currentTable,
(row) =>
if row[Level] <> null then row
else
let
match = Table.SelectRows(DependencyMap, each [TargetAccount] = row[Account]),
sources = if Table.IsEmpty(match) then {} else match{0}[Sources],
allResolved = List.IsEmpty(List.Difference(sources, assignedAccounts))
in
if allResolved then Record.TransformFields(row, {{"Level", each currentLevel + 1}})
else row
)

Re: Calculate Iterate Levels in M

v-priyankata — Mon, 23 Jun 2025 06:26:02 GMT

Hi Yaroslav__D

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Re: Calculate Iterate Levels in M

v-priyankata — Thu, 26 Jun 2025 16:00:18 GMT

Hi Yaroslav__D

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the solution would be awesome for others who might run into the same thing.

Re: Calculate Iterate Levels in M

v-priyankata — Tue, 15 Jul 2025 04:45:21 GMT

Hi Yaroslav__D
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. Please contact the community for any issues you may have. We are happy to assist you.