Forum Discussion
Cross Join from a single table
Morning everyone and thanks in advance for your support.
I need to create a table and call it "presence" starting from a single table called Res_Register.
Above mentionned table contains the the column "Res_Code" which represent a single "resource", while in the columns from AF1 to AEP1 are indicated if the resource is present or not. all the columns from AF1 to AEP1 are named as a date.
The table requested shall cross join the Res_Code with all the columns from AF1 to AEP1 and shall have three columns "Date", "Res_Code", and "Presence Type" that is the the value contained in the combination of the Res_Code with the related Day.
The table shall contain the list of resource present for each day.
From what I undestand you only need to unpivot the data, follow these steps.
- Load your
Res_Registertable into Power Query. - Select the columns
AF1toAEP1(all date columns). - Use the Unpivot Columns feature:
- Right-click the selected columns → Choose Unpivot Columns.
- Rename the new columns:
- Rename the "Attribute" column to
Date. - Rename the "Value" column to
Presence Type.
- Rename the "Attribute" column to
- Format the "Date" column:
- If the column names
AF1,AEP1, etc., are date codes, map or parse them into actual date values
- If the column names
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
- Load your
2 Replies
From what I undestand you only need to unpivot the data, follow these steps.
- Load your
Res_Registertable into Power Query. - Select the columns
AF1toAEP1(all date columns). - Use the Unpivot Columns feature:
- Right-click the selected columns → Choose Unpivot Columns.
- Rename the new columns:
- Rename the "Attribute" column to
Date. - Rename the "Value" column to
Presence Type.
- Rename the "Attribute" column to
- Format the "Date" column:
- If the column names
AF1,AEP1, etc., are date codes, map or parse them into actual date values
- If the column names
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
- Load your
Hi ToastMaker
Thanks for posting in Microsoft Forum community.
As per query Cross Join from a single table I've worked through your query and applied the logic to sample data for better clarity. Below is the approach and the resulting query for transforming the Res_Reregister table into the requested Presence table:
Below is the sample data have taken based on your query.
From above sample data I've created two separate tables Res_code And Date.
And perform the cross join between date and rescode table, in power query we don't have cross join instead we use custom column which works as a cross join.
In custom column selects the table which we want to cross join we want to cross join Date with rescode table.
After cross join will get a new custom table by selecting the expand () the contents of this newly created column, and then select OK.
After selecting the res_code from expand column cross join is performed.
NOTE: I hope this information is helpful. If you encounter any issues or need further assistance, please feel free to reach out.
