Forum Discussion

LukeReds's avatar
LukeReds
Helper II
3 years ago
Solved

fill table cells with POwer Query

hi to everyone, i have a table like the first pic, i need to fill the cells like in the second pic. That is, the next month that doesn't have an amount have to be filled with the amount of the previ...
  • ronrsnfld's avatar
    3 years ago
    • Transpose
    • Fill Down (starting at row 3 of the transposed table
    • Transpose back
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RU3JDYAwDNsl7yK5F8VP5qj64Np/BVJ6IMWKfMTJWQAfvBg5FG0WB0gxnxWtCqeCjD0wnMovhWdgXVtqy3FGkgr332tn7QrlT5MtIkbvDsD2I5LjY3kB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [article = _t, description = _t, Jan = _t, Feb = _t, mar = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"article", type text}, {"description", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"mar", Int64.Type}}),
     
        #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
        #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    
        #"Split off first two rows" = Table.SplitAt(#"Transposed Table",2),
    
        #"Fill Down" = Table.FillDown(#"Split off first two rows"{1}, Table.ColumnNames(#"Split off first two rows"{1})),
        #"ReJoin First two rows" = Table.Combine({#"Split off first two rows"{0},#"Fill Down"}),
        #"Transposed Table1" = Table.Transpose(#"ReJoin First two rows"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
        #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"article", type text}, {"description", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"mar", Int64.Type}})
    in
        #"Changed Type2"