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
