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
rbreneman
Helper II
Helper II

Transforming schedule data

Hi!

 

I could use some help on trying to transform data in Power Query Editor. I have data that is structed as shown below. As you can see the start and end dates could be a rather large range, not neccessarily a week.

IDStart_DateEnd_DateMonday_HoursTuesday_HoursWednesday_HoursThursday_HoursFriday_HoursSaturday_HoursSunday_Hours
0019/1/20229/3/20220004.5100
0029/10/202212/31/20221111100
0039/12/20229/23/20222.532.532.500

 

I'd like to end up with a table like this:

DateHoursID
9/1/20224.5001
9/2/20221001
9/12/20221002
9/12/20222.5003
9/13/20221002
9/13/20223003

 

I'd appreciate any help you can give me on how to make this work!

Thanks!

Ryan

1 ACCEPTED SOLUTION

Ok, that was a pretty simple change. Rather than adding 6 days, I just went from start to end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUN9Q3MjAyAjONYUwDJGyiZwokDeEisTrRShDlhgYw9YZG+sZwcwyxYIRWY4hWI4StRnBrjcBWGWNhQfXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start_Date = _t, End_Date = _t, Monday_Hours = _t, Tuesday_Hours = _t, Wednesday_Hours = _t, Thursday_Hours = _t, Friday_Hours = _t, Saturday_Hours = _t, Sunday_Hours = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start_Date", "End_Date"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Start_Date", type date}, {"End_Date", type date}, {"Value", Currency.Type}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Filtered out Zeros" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered out Zeros", "Start_Date", "Date Bracket"),
    AddDateRange = 
        Table.AddColumn(
            #"Duplicated Column",
            "Date Range",
            each
                let
                    varStartDate = Number.From([Start_Date]),
                    varEndDate = Number.From([End_Date])
                in
                {varStartDate..varEndDate}
        ),
    #"Expanded Date Range" = Table.ExpandListColumn(AddDateRange, "Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Added Match" = Table.AddColumn(#"Inserted Day Name", "Match", each [Attribute] = [Day Name], Logical.Type),
    #"Filtered for Match is TRUE" = Table.SelectRows(#"Added Match", each ([Match] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered for Match is TRUE",{"ID", "Date Range", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ID", "Date Range"}, {{"Hours", each List.Sum([Value]), type nullable number}})
in
    #"Grouped Rows"

So just for ID 3, I get this. Originally 3 was 5 rows, but spanned 2 weeks, so it makes sense to me it now has 10 rows. As you said, ID 2 is dozens of rows...

edhans_0-1663365709113.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

@rbreneman here is what I came up with:

edhans_0-1663361049239.png

Here is my code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUN9Q3MjAyAjONYUwDJGyiZwokDeEisTrRShDlhgYw9YZG+sZwcwyxYIRWY4hWI4StRnBrjcBWGWNhQfXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start_Date = _t, End_Date = _t, Monday_Hours = _t, Tuesday_Hours = _t, Wednesday_Hours = _t, Thursday_Hours = _t, Friday_Hours = _t, Saturday_Hours = _t, Sunday_Hours = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start_Date", "End_Date"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Start_Date", type date}, {"End_Date", type date}, {"Value", Currency.Type}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Filtered out Zeros" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered out Zeros", "Start_Date", "Date Bracket"),
    AddDateRange = 
        Table.AddColumn(
            #"Duplicated Column",
            "Date Range",
            each
                let
                    varStartDate = Number.From([Start_Date])
                in
                {varStartDate..varStartDate+6}
        ),
    #"Expanded Date Range" = Table.ExpandListColumn(AddDateRange, "Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Added Match" = Table.AddColumn(#"Inserted Day Name", "Match", each [Attribute] = [Day Name], Logical.Type),
    #"Filtered for Match is TRUE" = Table.SelectRows(#"Added Match", each ([Match] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered for Match is TRUE",{"ID", "Date Range", "Value"})
in
    #"Removed Other Columns"

Here is what I did:

  1. Unpivoted all but the ID, StartDate and End Date. That gives me a clean table.
  2. The Attribute column now has your "Monday_Hours" field name in it. I got rid of the _Hours part.
  3. Filtered out 0 rows for the value.
  4. Now I added a date range based on the Start Date Plus 6 days, so a full week. This is a nested list in the "AddDateRange" step.
  5. Expanded that and changed the Date Range to a date data type.
  6. I inserted the name of the day based on that date range.
  7. I then compared that day name to your Attribute (your old column) and only kept those that matched.
  8. Removed all of the unnecessary columns.

 

If this isn't what you need, please be more explicit in how you expect to arrive at the results. Your example wasn't 100% clear to me, but my solution makes sense. It may not be right for your needs though.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans ,

Thanks for your reply. That gets me close, however for the rows in the original table that span multiple weeks I need to be able to reflect all those dates in the final solution. For example ID 003 in the original table has a start date of 9/12/2022 and an end date of 9/23/2022. This spans two weeks, so the pivot would need to result in 12 dates (excluding zeros). Essentially in that example, there would be a 9/12/2022 of 2.5 hours and also a 9/19/2022 of 2.5 hours. ID 002 is a much larger range going all the way to the end of the year, so it would encompass all dates in that range. Basically the hours for each weekday would need a coresponding row for every occurance of that weekday during the date range.

 

Hopefully that makes sense and adds neccessary context?

Thanks so much for your help!

Ok, that was a pretty simple change. Rather than adding 6 days, I just went from start to end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbLUN9Q3MjAyAjONYUwDJGyiZwokDeEisTrRShDlhgYw9YZG+sZwcwyxYIRWY4hWI4StRnBrjcBWGWNhQfXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Start_Date = _t, End_Date = _t, Monday_Hours = _t, Tuesday_Hours = _t, Wednesday_Hours = _t, Thursday_Hours = _t, Friday_Hours = _t, Saturday_Hours = _t, Sunday_Hours = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Start_Date", "End_Date"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Start_Date", type date}, {"End_Date", type date}, {"Value", Currency.Type}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Filtered out Zeros" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered out Zeros", "Start_Date", "Date Bracket"),
    AddDateRange = 
        Table.AddColumn(
            #"Duplicated Column",
            "Date Range",
            each
                let
                    varStartDate = Number.From([Start_Date]),
                    varEndDate = Number.From([End_Date])
                in
                {varStartDate..varEndDate}
        ),
    #"Expanded Date Range" = Table.ExpandListColumn(AddDateRange, "Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type1", "Day Name", each Date.DayOfWeekName([Date Range]), type text),
    #"Added Match" = Table.AddColumn(#"Inserted Day Name", "Match", each [Attribute] = [Day Name], Logical.Type),
    #"Filtered for Match is TRUE" = Table.SelectRows(#"Added Match", each ([Match] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered for Match is TRUE",{"ID", "Date Range", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ID", "Date Range"}, {{"Hours", each List.Sum([Value]), type nullable number}})
in
    #"Grouped Rows"

So just for ID 3, I get this. Originally 3 was 5 rows, but spanned 2 weeks, so it makes sense to me it now has 10 rows. As you said, ID 2 is dozens of rows...

edhans_0-1663365709113.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans thanks so much! This is great! I did some quick validation on my dataset and everything looks good. I greatly appreciate your time and explaining the steps you took to get to the solution.

 

@AlexisOlson Thanks for your insight as well. My dataset is small enough right now that the run time is not of concern, but this is good to know so I can build my report to scale.

Glad I was able to help @rbreneman 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@rbreneman It looks like @edhans nailed it.

 

I just wanted to add that if your dataset is large, then you may want to filter for the day of the week before expanding rather than after so it doesn't need to expand into as many rows. It makes the AddDateRange step more complex though, so it might not be worth the extra effort if your data is small (though it does eliminate the need for a few of the later steps).

AddDateRange = 
    Table.AddColumn(
        #"Filtered out Zeros",
        "Date Range",
        (r) =>
            let
                Days = {Number.From(r[Start_Date])..Number.From(r[End_Date])},
                Dates = List.Transform(Days, Date.From),
                Filtered = List.Select(Dates, each Date.DayOfWeekName(_) = r[Attribute])
            in
                Filtered,
        type list
    )

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