Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Loop / recursive / DAX / PowerQuery / Same Table loop

Hi, I am new to Power BI and I need help with creating a loop using DAX or Power Query. Problem Statement: I have only 1 table (which shows unidirectional links between workitems) with 4 columns. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

here the

 

 

 

 

let

  Source = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lEyMgISwSX5RZUgnltqYklpUSqIaWamFKsTDZE3NkaVcy3ITEZSY2ICZJuaAomQjNRcsApfJ08QZQCWB2sHK4JphKuDKADrBRoG1+ecWICQNYQ6FO5SI4QKkChUDZQLV2UMUWUEEwYrg9kDVmGCMAfqE3NzhCEgtxohzAC6JRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sid = _t, pid = _t, sit = _t, pit = _t, #"CapID (OUTPUT)" = _t]),
      #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
          {"sid", Int64.Type}, 
          {"pid", Int64.Type}, 
          {"sit", type text}, 
          {"pit", type text}, 
          {"CapID (OUTPUT)", Int64.Type}
        }
      ),
      #"Inserted Merged Column" = Table.AddColumn(
        #"Changed Type", 
        "Merged.1", 
        each Text.Combine({Text.From([sid], "it-IT"), [sit]}, ":"), 
        type text
      ),
      #"Inserted Merged Column1" = Table.AddColumn(
        #"Inserted Merged Column", 
        "Merged", 
        each Text.Combine({Text.From([pid], "it-IT"), [pit]}, ":"), 
        type text
      )
    in
      Table.RenameColumns(#"Inserted Merged Column1", {{"Merged", "col2"}, {"Merged.1", "col1"}}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"col1", "col2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1),
    cols=Table.ToColumns(#"Added Index"),
    #"Added Custom" = Table.AddColumn(#"Added Index", "linked", each List.Last(List.RemoveNulls(let cr=List.Skip(cols{0},_[Index]) in if List.Contains({"Story", "Feature"}, _[col1], (x,y)=>Text.Contains(y,x)) then   List.Accumulate({0..5},{_[col1]},(s,c)=>s&{try cols{1}{let pos=List.PositionOf(cr,s{c})+_[Index] in if pos <_[Index] then -1 else pos}otherwise null}) else {"0"})))
in
    #"Added Custom"

 

 

 

the version that uses an index column that confines the search to the rest of the table, as you proceed.
This obviously works if the linked groups are not intertwined, in the sense that the components are in the following order

Story -> Feature -> Epic -> Theme -> MBI -> Cap.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

here a code which follow the same logic of my penultimate message.

 

I used the "trick" of the list.buffer function to store the two columns on which repeated searches are performed.
This seems to have an excellent effect on execution times.
A few minutes for a table of about 500k rows

 

############Edited############

Used list.buffer on indexed version, that seems faster.

 

Follow a randomly generated table; a funcion that is udes to generate that table and the code to perform the required tarnsformations on the table rows  (665k).

 

 

sorgente

 

 

 

let
items={"S","F","E","T","M","C"} ,
seq=List.Transform({1..200000}, (r)=> List.Transform(List.Sort(List.Distinct( List.Transform(List.Random(7,r), each Number.RoundDown(_*6)))), each items{_}&Text.From(r))),
seq1= Table.Combine(List.Transform(seq, each Table.Transpose(Table.FromColumns(zoo(_)))))
in
    seq1

 

 

 

 

 

zoo

 

 

 

let
zipOddEven=(items)=>

let
itemsL=List.RemoveLastN(items,1),
itemsR=List.RemoveFirstN(items,1)

in List.Zip({itemsL,itemsR})

in
    zipOddEven

 

 

 

 

 

 

 

 

let

    Source= Table.RenameColumns(sorgente, {{"Column1", "col1"}, {"Column2", "col2"}}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"col1", "col2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1),
    cols=Table.ToColumns(#"Added Index"),
    cols0=List.Buffer(cols{0}),
    cols1=List.Buffer(cols{1}),
    #"Added Custom" = Table.AddColumn(#"Added Index", "linked", each List.Last(List.RemoveNulls(let cr=List.Buffer(List.Skip(cols0,_[Index])) in if List.Contains({"Story", "Feature"}, _[col1], (x,y)=>Text.Contains(y,x)) then   List.Accumulate({0..5},{_[col1]},(s,c)=>s&{try cols1{let pos=List.PositionOf(cr,s{c})+_[Index] in if pos <_[Index] then -1 else pos}otherwise null}) else {"0"})))
in
    #"Added Custom"

 

 

 

 

PS

I would like to thank mahoneypat for your kind words to me, but I would like to specify that I do not use power queries for work and therefore I have almost never tried the scripts that I produce on real tables.
So I have no efficient code experience, moreover my experience with power query is quite recent (due to the lock down covid-19), therefore my indications on processing times could be very approximate.
Unlike mahoneypat , I can't even try different routes as I know absolutely nothing about DAX and therefore I have to try to do everything with M 😀