Fill down with conditions

[jeongkim]

HI,

Anyone please help me that I would like use Filldown Mcode but with conditions.

If 1st Column value is not Main 5G, then No need Fill down, only when it is Main 5G. 

So Other PJT or Sum don't need Filldown.. to be as it is. 

[v-yiruan-msft · Accepted solution]

Hi @jeongkim ,

You can follow the codes to achieve it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVHLTsMwEPyVVU9BakVoKeo1fciEkjapDURUPRhnEZESB9luj3w72whVQA0cQEgryzszO+PHet3pdNvadD9s+2F/AEm8OGUc8jz/JZOaptgq9y2SyFLDkFFLFrRenItVDkGUZeLkk+BHdg/DWRgmIIui12ioSoXaokd5n/FgJEYrn8tlNj9Gh2x6Q8Gl4UpWCNE4Xnpm/0zF+P9nzpJ96AJdpBxcf/V0b/xYWgxirSqYFah7hHpC32kh1tbJSrqS/oWj2ZF/O8C39SG7BZbuCQ2kV+IA8ztI5+yIpErRPDamllpRQP1smnKHNWoHaTPx6QVa94AF0Bmnt8KniJRCa2EiDUIwYC9DRvfavAI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.Data.Column1 = _t, Custom.Data.Column2 = _t, Custom.Data.Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom.Data.Column1", type text}, {"Custom.Data.Column2", type text}, {"Custom.Data.Column3", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Custom.Data.Column2", "Custom.Data.Column2 - Copy"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Custom.Data.Column2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Custom.Data.Column2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Up","",null,Replacer.ReplaceValue,{"Custom.Data.Column2 - Copy"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Custom.Data.Column2 - Copy"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down1", "Custom", each if [Custom.Data.Column1] = "Main 5G" then [#"Custom.Data.Column2 - Copy"] else [Custom.Data.Column2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom.Data.Column2", "Custom.Data.Column2 - Copy"})
in
    #"Removed Columns"

Best Regards