Forum Discussion
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 previous month.
How is it possible with Power Query (no Dax)?
Thank you!
- 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"
3 Replies
- 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"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!
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.
