Forum Discussion
Solved
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...
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"
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"
I'm impressed. This is exactly what I need. Thank you very much š