Create Rows From Each Row

posted in Power Query on by
Share The Knowledge

Each row has Start and End dates. We need to create rows for dates within each date range and split the Amount into those rows.

Requirement

We start with this Excel data:

Excel source data

For Item 1 create a row for each date between Date Start (2013-05-22) and Date End (2013-06-09). Split the 20 between 19 days.

End goal is to create this output:

Amount is aggregated by ‘Year’ and ‘Week of Year’

Original total of 60 (20+10+30) is split out then aggregated by Year/Week.

Solution

Let’s review key steps (full M code listed further below).

Inserted Date Subtraction

Number of full days between [Date Start] and [Date End]

 #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Day Length", each Duration.Days([Date End] - [Date Start])+1, Int64.Type),

Inserted Division

Amount split between the number of days in each date range

Row 3 Amount = 30. There are 6 days so each day gets 5
= Table.AddColumn(#"Inserted Date Subtraction", "Division", each [Amount] / [Day Length], type number)

CreateDayList

Magic! Column Date List contains a list of dates in each row!

= Table.AddColumn(#"Renamed Columns1","Date List", each List.Dates([Date Start], Duration.Days([Date End]-[Date Start])+1, #duration(1,0,0,0)))

ExpandDateList

Magic! Create rows out of each list! From 3 rows we now have 53 rows!

= Table.ExpandListColumn(#"CreateDateList","Date List")

Complete M Code

Some steps are basic, others (listed above) contain key concepts that perform magic! Creating a list of dates in each row and then expanding each list into rows are the key steps!

let
    Source = Excel.Workbook(File.Contents("C:\PBI local files\0002 Extract Transactions\Items v1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Items", type text}, {"Amount", Int64.Type}, {"Date Start", type date}, {"Date End", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Day Length", each Duration.Days([Date End] - [Date Start])+1, Int64.Type),
    #"Inserted Division" = Table.AddColumn(#"Inserted Date Subtraction", "Division", each [Amount] / [Day Length], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Division",{{"Division", "Daily Amount"}}),
    #"CreateDateList" =Table.AddColumn(#"Renamed Columns1","Date List", each List.Dates([Date Start], Duration.Days([Date End]-[Date Start])+1, #duration(1,0,0,0))),
    #"ExpandDateList" =Table.ExpandListColumn(#"CreateDateList","Date List"),
    #"Inserted Week of Year" = Table.AddColumn(ExpandDateList, "Week of Year", each Date.WeekOfYear([Date List]), Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Week of Year", "Date List", "Date List - Copy"),
    #"Inserted Year" = Table.AddColumn(#"Duplicated Column", "Year", each Date.Year([Date List]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Week of Year"}, {{"Weekly Item Amount", each List.Sum([Daily Amount]), type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Grouped Rows",{{"Weekly Item Amount", each Number.Round(_, 2), type number}}),
    #"Sorted Rows" = Table.Sort(#"Rounded Off",{{"Year", Order.Ascending}, {"Week of Year", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each 5),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"})
in
    #"Removed Columns"

Summary

In this post I wanted to practice List.Dates and Table.ExpandListColumn If you have a different or better approach please add a comment below.

Key M Functions Used

About Me

This is my personal blog to practice learning Power BI (Power Query, DAX, etc) and share my knowledge with you. For now I’m concentrating on creating content. More about me later.


Share The Knowledge