How to replace the value of the second column that appears in the first column in other rows?

[MrJunato]

Hey community,

I have a 2 columns table in Power Query, an it objective is to show which tasks depend on others:

Task Code: It's the code of a task

Activity Column: It's the task code of the task that needs to be done before the Task Code of the column 1

I need to to this on Power Query:

Sometimes the task of column 2 also depends of another task, so the information need to be completed, for example:

The first table says that the task code 3 depends of the 5, but the five depends of the 1, so the right to say is that the task code 3 depends of the tasks 1/5

I've created the logic in python, but I'm having trouble refreshing the dashboard that uses this table so I'd like to do this using Power Query M.

But summing up what I need to do is in the second column replace values that appear in the first column in other rows, using the values in the second column of those other rows

Can anyone please help me?

Thanks for reading my topic!

[jbwtp]

Hi @MrJunato,

Try this one, it does what you need, but may need to be twisted a bit depending on your column names, input types, etc.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzclRitWJVjJC5hgDOaZglgmysCmQYwhmmQFZRmCWObICC5CwviWYbQlkG4NZhgZApqW+mVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task code" = _t, #"Dependency With" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task code", Int64.Type}, {"Dependency With", type text}}),
    #"Parsed Date" = Table.TransformColumns(#"Changed Type",{{"Dependency With", each Text.Split(_, "/"), type list}}),
    fTrackMe = (d as list) =>  List.Accumulate(d, {}, (a, n)=>  a & (if n = "null" then {} else {n} & (try @fTrackMe(Table.SelectRows(#"Parsed Date", each [Task code]= Number.From(n)){0}[Dependency With]) otherwise n)) ),
    #"Added Custom" = Table.AddColumn(#"Parsed Date", "Custom", (x)=>  fTrackMe(x[Dependency With])),
    #"Added to Column" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.Combine(List.Sort(_), "/"), type text}})
in
    #"Added to Column"

Kind regards,

John

[MrJunato]

Wow it was a very smart solution John, thank you so much!

But the code doesn't work with because of a stack overflow error: 

Expression.Error: Evaluation resulted in a stack overflow and cannot continue

Maybe this is happening because my table is bigger then the example table and the loop become giant, what do you think?

Thank you so much for your solution and attention

Thank

[jbwtp]

Hi @MrJunato,

Could you please check if you have any "cyclic" references? I think this may be the most probable cause of the issue as the code does not check for self-referncing.

Thanks,

John

[jbwtp · Accepted solution]

Hi @MrJunato,

I am sure, this is a cyclic references issue. This is a larger set of data (1250 rows), it calculates and loads almost instantly:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZxLkiwpDkX38sZlBhIIobWU9Q7Ketb7b9DHMy81Szw8POQCdPRB+ffff+jPX3+UZxuL/vznr7//8Bn/93///OODcQYm3f+e529ZTUh9KGc412rEnf3COheI9vKB/n7KPoNzp854kP3+jDqMrjSDrY1pceGKo+P+zIifoSvTXjsG0x8dEpHAo1wea0p5K4hEG0ZXJImnMAjEBCNQDl9JpNMRNr864WMQh684ckT0wZVFLRUOojBoZ3QXLN58XFlsx7cGztOA0ZVD92wy4vcGiDJipiY36nPEJVDOuBLJ0LbzxUboZ7c549cnKGmCkiZINkGyeSWbO546Qaq5YAQCTVDRBBVJ93mWRqTxMkL+I7vRUYRfuBLZDC3KFYhH45VjmDQBmQRkEpBJQCa5Mq3dj0rzRxeoaIGK1hVoztgqCzS0YkWfuaHcVuK6D5UtEGiBQAsEWqAkdSWRxKZSEEZhvhSk0SvNmHRWQkijoB8FcRTEURBHrzhsoZoNqtkgzQZpNkizYao2iLKXz/Fo31tutEHbt1y8xQblWConhLMrTpkxA2kMpDHXTd4HstiVZSk36WF77IqyZ2+WttLcJFIIao9B9D3PabU6+bOO3c11Rd1NYq71s9zwy77B1mhD6wFoFfvCoVui0XOEVrH7pI18kFtqXdosFyMRzBy5raazFXOIktHEYSzrtPKEUj22+jHWhPpyg63HDLPma6DRJrfa0/bdUvkubrrVUodut6XeE+02MYrmpntrb8KUV7Zv5nxrtN/kBnz3VNhAwdCGExpxGqgwtOHkRnyRHjDSykuotYFaGygYWnByE77SItGMmTzY3fWWaMfJDbnVS7slp9zdhJac0JST2/KVYCG35bNfvicbKQz60dWxpfVIQcUJKs5Nus9Jbi9Co05o1QnNOrldp3Iv0K5TGHbmxrX/3LDPXqMrmUiunYWCuWmf63Az7Qm5eT/LfbZP2etxW65wg+vxqDy08YRGntzKj7nbrs2Plp7c1Mu8Wzgv4Kwqqs2NvVk9C7Xm1n70a3A1hUWTT27zdf7ed275j9HJqUbTT2j7yY3/LEOHxp/c+iunDjfKth9f78q1a3258SdKJ4Tc+q/60K3/PJ4Oz3wpRAAhA8hQKucAS0rlHGDe13lNQ2comT1uKMwnd3RFHQXH/Gvj3JXc0SFFGHBHfxRJwEgCDhLwakb1cHRMOwpHj58czulZ6uXjEsrmONA+GydoGYnA6L6zI2GtniPFD92I7HoQSuZAOEb9OmMZRvDrx7NvsuMFZnTAToTJI0e+TUe69Y8nj0RgJ8JY9UNOg2+EkjkNrKeCnAbfQmSnAeuJWHK3sAOBKOnMCAR2IAyhNktJAwVDHrDz4E6wVqSDSOCZm/MY3kQKOxYqTkHHnmeEPymrA0GHtbVyVhwKXMIjFBihwOjgM3r47EQw94HyAs4m4oAFd4GzQKeclcl5xW3HiQ0ltyU7EdKMMfr57ECYB0dbcuKcCZa+JKOjz+jp8wqU7vP1Uio6/JxIOEovRa8nckTVORKktwyB2ZnwRZaOBDkO4EwWsqJ8iAR2JCjLES9f35lglsI6EozOTsnonZ0Kg2rkNk7Ptq6ZxjCAHQojXV12Jtg6Vmam+BgM8I5NcSaHte7A6XUscM2FY+EQqGkK7GDYxx2UUfE16g/hwA6HqdJW7UsHxIHk7il0BAjJazZUoPNBSnmOh231xQifcrthkMBOB83YnhEM7GAwq6c+SYGOQ8IhZgaQCcOZMJL/A5EwEAnDkXD86/kT1Q6HwjEtF+r1EJTOwWByvJD0r0dmdfgs2fyKs2HIiZByYQyMFoaz4RjCJpXvQDwMjBgG8mE4H7iyF8iHgRme4WiQeaxBehMD0zwj8jz5/hErpFMynAwHyftLJwykw3A6yNk/kst5OCFs12+hbCNs8JJmpXEMGsab+HHhxM58ZG5mOCdm/ZxjIuOs4YQQWe1+L66ENamvbnw4CheAoFITpn0G4mHMJyXl+9TaroxTxgz5w4iHgXgYjod0GgfCYQjOpqNhc74vkmEgGQZGCQOjhIFRwnAqzJsArXQcRgrDqaB2VnnydTgZJOPhgWAYDobV78RpKsHBsNKQjYXSIRRGhgl8rFRe8MD0LLRaBs6FTNuMFfmpYxXT4RmOhbUOl0qxSIaBeaGh6Yzo2cUpobOBuNcTPSWcgdHQQOqND2o2nAxUAjsYrHZSgCFt7EAojIRChmjDgcBJoBFhwlHk7vksZ8I+xK77M1edIyfWqJFPa6ps44w6CNZ5cCrIUCoHwWfAnANzHFu5UwxnwQ03YhTposP52pnIg+E82LUTkQcDA4XhQNidm+VbTWTCdCZo5h2mI4G6eJhd13yxUas09MRoYSIapqNB08jNjulZjBSmA4E4X2UGENalSDpwEzNHE0OF6SwosTBKmIiBiRiYjoFjO7/fQcEoItL80DlwYNqm5G9hlDCRA5Mxlc2xyo5nxKkVp4GINMngcSIMJqPaMPM/EQVz4IyOyNBkLcYpcKau3fRBXEHpMFiYToE15xc6zkwgWY4C9akZxMBEDEzHgOT2m0/yP1JHPT2g+ab/UaqnAvCUAJ4agEPA+k0naD3dnd3026ZEru1YwFo7AYNM7E2EwXQYkNSH7nBYb9rrSrA9TdJEHEwMEiayYApqDEOEiSSYTgLhfAuHgNYCyuDguKflMcwVcmVwOh0EMvhs5NQK5ozmiuzHift2vhmmjSYGCBMxMB0DI432TAp0bap1yfdB/raGcDt3BOaMJoYGE/NFU1EsZ8DMquTEVNHEmGDuiFmsWcJsBgOqdLkz4suR1wpK4UGAUrgj4Mx6ahcrBdMJsE+MTlz6x3BgYrpoOgOqBDkxWzQt7MUJdVJMq8pcDlFdzgCpPYoxgbj9P+uu7ayGCoYFgmGBuPnn4y5QTzstCADpgfOsqWFoIAgAcQBUxUowIBC3//tw9KyLLMih/RfiFCYrC+IEIOYq6KFkzoDDl3GLz3kJxcNwQBwD+xBIcmGKkyCnRRwE9hUL3WjkbhNkgCADxBkw0hwJ5okkSr7nvTkjUUH7L2j/Be2/uP2vIFgwChCMAiSigH2dC8krqLIRKqPLprrDxavJx2SROAHGCdNTv46As+2rZgomQ5wBNyyNEcrmCKC0qoIEECSAOAE0/S1xAKyMImX+rrPIUwR22z/OEssdLxJ1s7wbTb9EDdhGW6tueKrAKJbkvjz+U891IRE3rTYTqOIIoGMYM6kkCAFBCAhCQFaY2d1GxguCaSLBcEAwHBCngCm1SmEKUkCwciCIAHEEWAb04gSQzACJBtG/D113x5NYVUDHqoFgiVg0/IzVVi0epIA4BajKKxKV4jIUDoGdzrrsSEemgpAB4gw4QfBhQE5AxgGr8fcV+fMrghXMDImTQKR+OgpV122t55nPUX6O0YBE5cB2kwz/xFFQSWTBwoE4Cs6GSkkcBXsdhz6TvxLhQE0CokAwHBCL3ZnJ7oWxwHIQFMFXlI97jfDwASJgOQL2dpdL8hKeQEAOLOfArFstNnPLpbOigpzrezkDTnDw5dkWhgErKgap7oUMWMGAE0y2Vc9z25Gu30ICLApAlWiYEVqYEVoYBiyOFG6ns4RTbRxuUO61FRjYORD8ts+j3Lg83wQpsJACCymwggJMbWQwt6JswPMmjFNxSIM1gJ5rZHn7LORMcS8sJi8sHKwIBshOtJC1oDVik876vk9taQNpsJwGet84LeFCIiwMClakhqrmsBAJy5FwlM9NMmW8MDBYWDxYMzbDed3MLSxMEa04HzTrQ4/b200VxRiFkwjb60P3ic6raa26RENqAqOChVHBQiCsFbm+45Wkmp5DQlhMXiui4pwBx8GQebz6fBEkwlq/Ubqeo0IrpjM1jjBYGA8sjeztbDPzVAszQytigp71iJVZoZt+qfWJSFiBBNEmadgXImFhYLAcCTuT4QtzQ8uZUE7CQiYsPD+0HAiVnlt4gGhlVJCDqm4L18+iTBgYLITBikNE85i+9HsWxgULabAwMFgWUVQCc1mEeLnGHAWVv1oW8Uqm2BZGBYok0J6m4zItTaE6Dw62qvyhiARFJCimhRRxoI6DdbZeHRfDzJBGZqhnUKdYQ1aMCtRxkDtPKS3u8XCSXUqxNccPndSpMIm+SoM6FTZzG7nh1clwNDZzGGS4T6H6LVQi0kE5y/B50DVOF90KQJ2uCzj0nC/FU6GKeNAIEtKuKqJBHQ21hBTJoCMjvhyh7pAIGkTo3BsnjNWpsOstEAgaR0T5+Lv1eSiNrM3kt2KSSDFJpMgEjfNF92BvGhFFJmhUk3MbK0YJikjQiBJqGSANFGmgQQOu2UYUqGRiOTXsLFCpe3EjYIigmR4yaZpBqGKKSB0GQ4+n/N3g83lmodaiE+HEmC1DUcUgQeN0Uc4Y8kBX7Ai9QUk+H2MEjaOjx/XelL+HpWR1Kmhm7BVDBH1OkCIV9DlDGkEC5bkHfU6RahK+ihWqcTZAW5Wp9D1KipMatYJ7yDfrVupMqPhOkQmKySLdcdqDmpZZQi4oni3SiBKM285IQjFQ0B0OUpkxR8PxY0eNUXPIBrU4cX/PK+bmcjZUzUoRDYpoUCwaqKFgGCYohgmKcNgIhx1wyIB/Y7po9/DF60OvMGaObPfILdxjZRlnbccC32OomT7dTgbmQ0SuQ8AeX1l9Hh5l44Tedjbc2rNk2nI7HjTrFztPm96qcsmMNeQdzQHzOGujnulHF1Y9b+HteF6Y4sBwb1Xs3VE9qG8jFXZQgbu2kdWEjamj7VgYctaJ1CPinP4tMKWACIeN5YONdNhOh087Toc62rPjpFEmsXZ2DuSdyIbtbDjSNWKuO/CgNcJhV/9AHrN2NNg6aEnrvSNYSA95OxjWPT+TRNzOhuPYN8v+hI1FhI1s2Bgv7DhqdCPtmpk4alRPR6U5Ho5BapKR5HZCjBrgnnA+3HMHVnfjWaONmaSNjNjIiI1ppI182M6HqkZtZ0MVfzeGChuxsDFQ2HnG6CytSnRsTB5tBMN2MFgtOoTCXnnqg36Oz+2IFypbsVe4cVwH7VE6RMPGaGFntJByYu5oOxfqJPZGJmxkwq68Ue42zbCPW6YA9o66425VB91RR9YaoWBPr0EcLOrpfO2n22Bn9axVUXo7E6xmE6OFjUTY1stijrIFUUxOum2MFjYiYSMSNiJhIxI2ImE7EliOh5x5y21RRMvuhx5BqVjTPMZqWEgwJINhuGCOhrOBv4MGhhGDYcRgccZo1K/HmQVumuS1iBiqp8OcCuseUexZdTWMGiy5kC6RIROMwhPZJ5rNhWNYUbboG7tzQ0zVEhJCjhx5NSEDDKMsdt9TvplxMY6zFf2maFILmFMyZINVyFBdJ56xyb1sCAXjEDD9eHMo2N2to+7PA0bty3cZhg6W51DP8qsWFAwfbOSpxeOYJu/MMbFKB0gIQ0JYHjM67hrXt/10Ss5J8KFORljUFiq3ZFFetp9z5eZ04Ho9hIMhHCwaE+5hkPxljB0M6WAYPBgGD+ZwKLfOMHYwB4NUo5BzQTIXY4gFQywYYsEQC4Y5JIuyQq+Jiogh/kYq2AoHbrdRuwUjBnMoyJJWpRpDLliUlulMXDlJhnAwh4MnECgBaRg2mLNhDK7zqYZ0MKfD2d5tZXbIosKcltMwdLDoQBP3TnIeMJtkun4BxRASphFB5wB3AQYNhkGDOR12epsWh4zsuCw5RsUhHczpcEWmavdCqeLM6QmvJUMIizOn6b0ZRgzmbDgOcasjl/b0pVmkBqudLNzKVH8UmTu3Wdsp2tPueZz0nwwZYRg22L/60/ozjlIb19CLuVLdam+DWgCifx/7wZTr5q9qQntb1LDG4Ei+1uuuwVTfufbI+LQVd4qkzTze9y5Bsdxwxo+kT8dad1DwiVkzZjlXPMzpFxWz+vEoUmA1it1bzSbdaXHMa6PvIfyI+nSvdX7E5EdMfsR8+tc6V6fYJyJHXXX+dMz1iCXsZ024dwfPGY+c2b7Av+r65+Ij7IjDed/HM2a7hqHRszarkaePUOmFF33XItzW/kv1T3dbT4jQTW1q3fQ0ufUESS2A+Qg7Q9jvY49y+R69qSuPah0m46C/joD5wZGoQg76LrkN2p9Qj17lETIq15lJOUO3Q6UxeSY/mxtqzqI+QSfu5lpxUaBg+p737CbJoxvf/R6uHa/54iIurUfC7HmzVg2959KjykBNNfH39azRFWdLvjP658qz6TMK+Z7/TPfT+db1EfFpfOvR5qByi7k1EU6acf2kXnLGWaaziNYuwfSZ86cNrquCPXt64Lo+gu5HUKfOcHenmvL6ThfWL9Y1N/F9tPWtBWeQuPyf7dyPtE6i6h53O3TPL3Hj6tLt+xF4PwJHrLJLjohTvtEz7Q6iUW2h/emO61Hl3tIq0PHZwFue5WkRrGuzr0nZopc8t1f0S1cVjAijFSIMV+htmCasb9DbL/02TNNDo7dnupqmz0SVnaH3f1xEr9w9KdLryiMkReNob6sM47/6p6OBukxTdFCT1zH2d+0R9e2jplDk+rEW0UxN3yh8tZtArmc+NKKshP98Hha++rofGL3d1NFOfeO5SoFR9FOznDXaq5P8QRE9KHr7qqOxet6TvbPkepqr3+7qt706+qvdR8jxo8uHP2979dtfHQ3Ws/yC6LAe1YD+dlfTfKSb8tsaRof16t/DPAS81Zo6bULRZm2ZCiHKoviqJzh0pH7+aa9++6vpoc7bXv32V0eDNffbkF3L17FTTTIUXdbze6HoqKsuYXqAE03Wl5zNypOiaqz7/oPAI+ODnGizplt7+UzFw5xotlauVZdJr317cb6vRJvCWZyrbnvQ8/ZcU56fOu67fv84wdEjdB2H7zGPvA933vbr7L/+NPhwhx7u0MOdt/WadhVwviN0FA3Y4r1esyYu0mJeyCvJnTmjXIHoxb617/XN9tOPTQ9z6GFO9GTL9272yPpQJ3qy5y55Iv4x+07cENmjzKioi5/tqql9yPP2Zkdz9uj1Xx8qUTa/JApFh/Y6IcIuu/x0aNPTok3Ro+2HcyrBQdGo/bm60ahduSjiB0BPmzZx1di/f4aBMx+d2hrHgb573Mk0/68zXNfCspcj9LRr09OvTdGwfRNR+/tPGg+Aom3bvgd6dkXkK+BQtm5TdePzv/6TR8h5u03rf3c84VC0bh9hvgPzxO9/9Hj/pQc/YnJmlksTD344m/TKd+UHP/wEQfywJxq5h2gbZR75wc/Tyk1PLzdFM/cwat9/Fni6uYkfAkUv9/fPQp5ubuIHQfwgiBNBVC/tDLLbh1yu5NPUTU9XN0Vb9yXUf/4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task code" = _t, #"Dependency With" = _t]),
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task code", Int64.Type}, {"Dependency With", type text}}),
    #"Parsed Date" = Table.TransformColumns(#"Changed Type",{{"Dependency With", each Text.Split(_, "/"), type list}}),
    fTrackMe = (d as list) =>  List.Accumulate(d, {}, (a, n)=>  a & (if n = "null" then {} else {n} & (try @fTrackMe(Table.SelectRows(#"Parsed Date", each [Task code]= Number.From(n)){0}[Dependency With]) otherwise {n})) ),
    #"Added Custom" = Table.AddColumn(#"Parsed Date", "Custom", (x)=>  fTrackMe(x[Dependency With])),
    #"Added to Column" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.Combine(List.Transform(List.Sort(List.Transform(_, Number.From)), Text.From), "/"), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Added to Column",{"Dependency With"})
in
    #"Removed Columns"

In the current state the function does not check for "biting its tale" cases. I will think on how to identify and eliminate them, but this can make the code [substantially] longer to run. 

Kind regards,

John

[MrJunato]

It's worked perfectly John, thank you so much, I've learned a lot with your code!

[jbwtp]

Hi @MrJunato,

I will try to suggest something else, just need a bit more time for experimenting.

The problem with stack is not thesize of the table (at least should not be), but the depth of the references. How long do you think the depth (the number of items) in the column "Dependancy with" in the output?

Cheers,

John