Forum Discussion

jsanchezm's avatar
jsanchezm
Frequent Visitor
6 years ago
Solved

Upon condition use value in row to fill another column rows

Hi, I have a bunch, quite a lot, of csv files that have the annoying property that the date of the registers is only in one value of the first row of each file (and in the filename). As an example, ...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi jsanchezm ,

     

    Create a blank query in Query Editor and paste this mcode:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2MNQHIiMDIwMgxxyILZRidaKVEoGsJCBOBuIUIE4F4jQgTgfiDDqqwOpko6HnZOOh52STwevkWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t, Col6 = _t, Col7 = _t, Col8 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}, {"Col5", type text}, {"Col6", type text}, {"Col7", type text}, {"Col8", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Date.FromText([Col6])
otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = null then [Col6] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Text.Remove([Col7], {"0".."9"}) = "" then null
else Text.Remove([Col7], {"0".."9"})),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if Text.Remove([Col8], {"0".."9"}) = "" then null else 
Text.Remove([Col8], {"0".."9"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Col6", "Col7", "Col8"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Col9"}, {"Custom.1", "Col6"}, {"Custom.2", "Col7"}, {"Custom.3", "Col8"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Col1", "Col2", "Col3", "Col4", "Col5", "Col6", "Col7", "Col8", "Col9"}),
    #"Filled Up" = Table.FillUp(#"Reordered Columns",{"Col6", "Col7", "Col8"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Col9"})
in
    #"Filled Down"

     

    If it works, use it on your sample file.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

just an idea:

you could replace "f" with null, then fill-Down the col6 and last filter one of column to exclude rows containing the corrispondent number "1" in col1, "2" in col2, ...

 

if you need somethink more generale, colud be useful the use of Table.group in this form:

 

Table.Group(Source,  "Col8", {"nnn", (r)=> List.Transform(r[Col6],each r[Col6]{0})},GroupKind.Local, (x,y)=>Number.From(x=y))