Forum Discussion
LukeReds
3 years agoHelper II
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...
- 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"
LukeReds
3 years agoHelper II
thank you, i just had to change a line of code (I have an old version of excel/power query...) and now your code functions perfectly!
ronrsnfld
3 years agoSuper User
Glad to help.
Be aware that #"Changed Type" and #"Changed Type2" lines can be rewritten so as to not reference the column names specifically. If that is a requirement, and you are having a problem making that change, feel free to post back.