Forum Discussion
Cross Join from a single table
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
Community SupportHi 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.