Move adjacent column data to the left if the current column is nil

Hi  vineshparekh 

You can put the of the following code to advanced editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjIwMtY30TcHMg0NjYwsDJVidRASlvpGRiC2MVwGJmwIUWJkagJkKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each _, type table [Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Value", "Index"}),
    #"Split Column by Position" = Table.SplitColumn(#"Expanded Custom", "Attribute", Splitter.SplitTextByPositions({0, 6}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom", each if [Value]="" then "" else if Text.Contains([Value],"/") then "Date" else "Number"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom1", each Table.SelectRows(#"Added Custom2",(x)=>x[Attribute.2]=[Attribute.2]+1 and x[Index]=[Index])),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom1", {"Value", "Custom"}, {"Value.1", "Custom.1"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom1", "Custom.2", each if [Value]="" then [Value.1] else if Table.RowCount(    Table.SelectRows(#"Expanded Custom1",(x)=> x[Attribute.2]=[Attribute.2] and x[Index]<[Index] and x[Custom.1]=[Custom]))>0 then [Value] else [Value.1]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom3", {{"Attribute.2", type text}}, "en-US"),{"Attribute.1", "Attribute.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"Value", "Custom", "Value.1", "Custom.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Merged]), "Merged", "Custom.2"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Column1", type date}, {"Column2", Int64.Type}})
in
    #"Changed Type1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.