Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone
I would like to create a new table (as shown in Table 2) from the data in Table 1.
The new table has one row for each candidate week end that is within the start and end date of the location.
Table 1 - 'Locations'
CandidateRef | CandidateName | PlacementStartDate | PlacementEndDate |
62896 | Jamie | 01/04/2020 | 01/06/2020 |
19382 | Henry | 01/01/2020 | 01/02/2020 |
66279 | Jorge | 01/04/2020 | 01/05/2020 |
New table -
CandidateRef | CandidateName | Weekend |
62896 | Jamie | 05/04/2020 |
62896 | Jamie | 12/04/2020 |
62896 | Jamie | 19/04/2020 |
62896 | Jamie | 26/04/2020 |
62896 | Jamie | 03/05/2020 |
62896 | Jamie | 10/05/2020 |
62896 | Jamie | 17/05/2020 |
62896 | Jamie | 24/05/2020 |
62896 | Jamie | 31/05/2020 |
19382 | Henry | 05/01/2020 |
19382 | Henry | 12/01/2020 |
19382 | Henry | 19/01/2020 |
19382 | Henry | 26/01/2020 |
66279 | Jorge | 01/04/2020 |
66279 | Jorge | 08/04/2020 |
66279 | Jorge | 15/04/2020 |
66279 | Jorge | 22/04/2020 |
66279 | Jorge | 29/04/2020 |
Solved! Go to Solution.
Hello @HenryJS ,
you can do this with Power Query as follows:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOysDRT0lHySszNTAXSBob6Bib6RgZGBlCOGYQTqxOtZGhpbGEEFPZIzSuqhEobIqs1Qqg1MzMytwQKu6fmF6VjNdgUqjgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CandidateRef = _t, CandidateName = _t, PlacementStartDate = _t, PlacementEndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PlacementStartDate", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"PlacementStartDate", Int64.Type}, {"PlacementEndDate", type date}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"PlacementEndDate", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each {[PlacementStartDate].. [PlacementEndDate]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Inserted Day Name" = Table.AddColumn(#"Changed Type3", "Day Name", each Date.DayOfWeekName([Custom]), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day Name", each ([Day Name] = "Sonntag")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"PlacementStartDate", "PlacementEndDate", "Day Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Week Ending"}})
in
#"Renamed Columns"
Greetings FrankAT
Hi all,
I would like to create a new table (as shown in table 2) from the data in table 1.
The new table has a row for each candidates week ending that lies withint the placement start date and end date.
Table 1 - 'Placements'
CandidateRef | CandidateName | PlacementStartDate | PlacementEndDate |
62896 | Jamie | 01/04/2020 | 01/06/2020 |
19382 | Henry | 01/01/2020 | 01/02/2020 |
66279 | George | 01/04/2020 | 01/05/2020 |
New Table -
CandidateRef | CandidateName | Week Ending |
62896 | Jamie | 05/04/2020 |
62896 | Jamie | 12/04/2020 |
62896 | Jamie | 19/04/2020 |
62896 | Jamie | 26/04/2020 |
62896 | Jamie | 03/05/2020 |
62896 | Jamie | 10/05/2020 |
62896 | Jamie | 17/05/2020 |
62896 | Jamie | 24/05/2020 |
62896 | Jamie | 31/05/2020 |
19382 | Henry | 05/01/2020 |
19382 | Henry | 12/01/2020 |
19382 | Henry | 19/01/2020 |
19382 | Henry | 26/01/2020 |
66279 | George | 01/04/2020 |
66279 | George | 08/04/2020 |
66279 | George | 15/04/2020 |
66279 | George | 22/04/2020 |
66279 | George | 29/04/2020 |
Hi @HenryJS ,
See the same thread I just replied below:
Pls note that the expected output about George has an error in Week ending dates,pls double check it.
is it possible to do this in DAX?
Hello @HenryJS ,
you can do this with Power Query as follows:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOysDRT0lHySszNTAXSBob6Bib6RgZGBlCOGYQTqxOtZGhpbGEEFPZIzSuqhEobIqs1Qqg1MzMytwQKu6fmF6VjNdgUqjgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CandidateRef = _t, CandidateName = _t, PlacementStartDate = _t, PlacementEndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PlacementStartDate", type date}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"PlacementStartDate", Int64.Type}, {"PlacementEndDate", type date}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"PlacementEndDate", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each {[PlacementStartDate].. [PlacementEndDate]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Inserted Day Name" = Table.AddColumn(#"Changed Type3", "Day Name", each Date.DayOfWeekName([Custom]), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day Name", each ([Day Name] = "Sonntag")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"PlacementStartDate", "PlacementEndDate", "Day Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Week Ending"}})
in
#"Renamed Columns"
Greetings FrankAT
Hi @HenryJS ,
Create a new table using below dax expression:
Table 2 = CALENDAR(MIN('Table'[PlacementStartDate]),MAX('Table'[PlacementEndDate]))
Then create a column as below:
Column = IF(WEEKDAY('Table 2'[Date],2)=7,1,0)
Finally create a measure as below:
CandidateName = IF(SELECTEDVALUE('Table 2'[Date])>=MAX('Table'[PlacementStartDate])&&SELECTEDVALUE('Table 2'[Date])<=MAX('Table'[PlacementEndDate])&&MAX('Table 2'[Column])=1,MAX('Table'[CandidateName]),BLANK())
And you will see:
For the related .pbix file,pls click here.
Duplicate of : https://community.powerbi.com/t5/Desktop/Create-Table-Week-Ending-Rows-From-Date-Range/m-p/1061648
Refer to my blog on power bi: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Have week end date in your calendar to display
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) //Sunday
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1) //Saturday
For Calendar refer : https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |