Forum Discussion
Calculate Iterate Levels in M
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
9 Replies
- lbendlinSuper User
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 - v-priyankataCommunity Support
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. - v-priyankataCommunity Support
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.
- Yaroslav__DNew Member
Hi , no, this issue not resolved .
yes , sure , mappingrules looks literally like thisTargetAccount 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- lbendlinSuper User
consider using Expression.Evaluate