• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move date to a new column in power querry

Hi, please help...I have a set of records that come with a date first and then a list of items in the same column (see first image.) I need to have a date move to another column and fill down the rest of the items that belong to that date and remove the row date, blank and grand total. Since it is a daily activity, I'd love to do it in power query. Please advise on how to get it done. Thank you so much!
Kim
 

Attachments

  • 2023-08-16 082416 Service Before.jpg
    2023-08-16 082416 Service Before.jpg
    64.9 KB · Views: 22
  • 2023-08-16 082416 Service After.jpg
    2023-08-16 082416 Service After.jpg
    52.4 KB · Views: 21
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Service Name] <> null and [Service Name] <> "Grand Total")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each if [Qty] = null then [Service Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Date", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Qty] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Date", "Service Name", "Qty"})
in
    #"Reordered Columns"
 
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Service Name] <> null and [Service Name] <> "Grand Total")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each if [Qty] = null then [Service Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Date", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Qty] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Date", "Service Name", "Qty"})
in
    #"Reordered Columns"
Alan,
It works!
Thank you so much.
Kim
 
Back
Top