Forum Discussion
Adding a column or table with skipped rows
Hi!
I managed to check how many "Nro. Comprob." rows are getting skipped. Now I'm trying to get which rows are getting skipped and I would like to add them to a new column or a new table, but I'm not being able to do it. I'd appreciate any advice.
Thanks in advance for any help!
try this
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWAzSyBpZGQM4jgqxepEKxmhS5nDpYzRpEzM4FImaFKmBnApU3QpqF2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [indice = _t, tp = _t, vita = _t, nc = _t, desc = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"indice", Int64.Type}, {"tp", Int64.Type}, {"vita", Int64.Type}, {"nc", Int64.Type}, {"desc", type text}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "NrComprCompleta", each try {[nc]..#"Modificato tipo"[nc]{[indice]}-1} otherwise {[nc]}), #"Tabella NrComprCompleta espansa" = Table.ExpandListColumn(#"Aggiunta colonna personalizzata", "NrComprCompleta") in #"Tabella NrComprCompleta espansa"
4 Replies
try this
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWAzSyBpZGQM4jgqxepEKxmhS5nDpYzRpEzM4FImaFKmBnApU3QpqF2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [indice = _t, tp = _t, vita = _t, nc = _t, desc = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"indice", Int64.Type}, {"tp", Int64.Type}, {"vita", Int64.Type}, {"nc", Int64.Type}, {"desc", type text}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "NrComprCompleta", each try {[nc]..#"Modificato tipo"[nc]{[indice]}-1} otherwise {[nc]}), #"Tabella NrComprCompleta espansa" = Table.ExpandListColumn(#"Aggiunta colonna personalizzata", "NrComprCompleta") in #"Tabella NrComprCompleta espansa"assume the Nro. Comprob column is the forth column, then try this
NewStep=#table(Table.ColumnNames(PreviousStepName)&{"Skipped rows"},List.Accumulate(Table.ToRows(PreviousStepName),{{},null},(x,y)=>{x{0}&{y&{y{3}-x{1}+1}},y{3}}){0})
