Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LukeReds
Helper I
Helper I

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!

 

tab.jpg

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

  • 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"

View solution in original post

3 REPLIES 3
ronrsnfld
Super User
Super User

  • 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors