Forum Discussion
lukinfo
2 years agoFrequent Visitor
Transform table - unfill with join text
I have a table that I want to transform as in the picture
I do reverse fill down (Unfill) using this metod:
https://www.youtube.com/watch?v=USwj-ueJOgc
I have this table but I don't know how do Info2 columnn? Is this posible to do ?
lukinfo
Paste the below M code in a new query and check the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sylbSUTI2ABKeeWn5Ci5KsTrRSkCeiRFMKAgmZGgJE3LGVOUOEwKbFZJaUaJgAtdoCBMyBwuF5OdVglSaAgmXxBIFR5hCIxOoCNxOYwuwSJFCCFyNGUgkNQ/q1JDMXJAN5lBBuMtMDaAijijOD88vQnK9OVQE7ngjiEgJQo0xWCQxT8EXxUnhidkgB8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Info = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Name"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Name"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Name"}, {{"Count", each Text.Combine([Info],","), type nullable text}}), Custom1 = #"Replaced Value", #"Added Custom" = Table.AddColumn(Source, "Info2", each if Text.Length([Name]) > 0 then let nm = [Name] in Table.SelectRows( #"Grouped Rows" , each [Name] = nm )[Count]{0} else "") in #"Added Custom"
2 Replies
- FowmySuper User
lukinfo
Paste the below M code in a new query and check the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sylbSUTI2ABKeeWn5Ci5KsTrRSkCeiRFMKAgmZGgJE3LGVOUOEwKbFZJaUaJgAtdoCBMyBwuF5OdVglSaAgmXxBIFR5hCIxOoCNxOYwuwSJFCCFyNGUgkNQ/q1JDMXJAN5lBBuMtMDaAijijOD88vQnK9OVQE7ngjiEgJQo0xWCQxT8EXxUnhidkgB8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Info = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Name"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Name"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Name"}, {{"Count", each Text.Combine([Info],","), type nullable text}}), Custom1 = #"Replaced Value", #"Added Custom" = Table.AddColumn(Source, "Info2", each if Text.Length([Name]) > 0 then let nm = [Name] in Table.SelectRows( #"Grouped Rows" , each [Name] = nm )[Count]{0} else "") in #"Added Custom"- lukinfoFrequent Visitor
I'm impressed. This is exactly what I need. Thank you very much 🙂