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

[vineshparekh]

Hi,

I imported data from a folder where there are few PDFs and all the PDFs have tables inside. However, when I import that into PQ, the data is not consistent. Some are pasted in Col2, some are in Col3 etc..

I wanted to ask if I can put a formula using MCode that move all the adjacent cells data to left if the curent column is nil.

For example,

Col2               Col3                          Col4

null                 07-Aug-23               112281

null                 22-Sep-23                232281

21-Sep-23      202254                     null 

Result Required:

Col2               Col3                          Col4

07-Aug-23     112281                     null 

22-Sep-23      232281                     null

21-Sep-23      202254                     null 

[v-xinruzhu-msft · Accepted solution]

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.