This site is a work in progress.
If using REDcap data, clean the timestamp variable. (1) Delete [not completed] using ReplaceValue function and (2) Convert the data type of your timestamp variable in Power Query.
In Fields panel, find your table and click '...' for more options and click new column and enter the following expression
"Week Start Date" = IF(
ISBLANK([timestamp_variable]),
BLANK(),
[timestamp_variable].[Date] - WEEKDAY([timestamp_variable].[Date], 2) + 1
)
Make sure to use .[Date] otherwise you won't be able to count by week later on
Make sure to use IF and ISBLANK() to handle blank dates
Youtube Tutorial Link: https://youtu.be/QL0p-SHb7Yg
Power Query
Home tab, Enter Data, create a table with matching code and labels - make sure the 'code' has a data type of 'text' otherwise a match won't be detected
Click on your table and click Merge Queries (Home tab, combine section)
Find and click the column in the original dataset (top) and click the code column of the new translation table (bottom) (leave Join Kind as Left Outer and Use fuzzy matching as unchecked). click OK
The new column will appear (with Table in each cell), click the expand icon in the header cell, and only select the 'label' option under expand.
Done
Open the 'Power BI Connect Template' - Power BI file with a pre-existing query and relevant parameters.
Create a new group to organise variables - right-click a folder on the left panel and click 'new group'.
Duplicate the query (data) and parameters, rename them, and place them in the folder.
Open Query Settings - View tab and click Query Settings.
Edit Applied Steps - select 'record' to 'record4', edit expressions so that parameter labels match the newly duplicated/renamed parameters
Go to 'Modeling' tab and click 'New Table'