Cross Join from a single table

[ToastMaker]

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.

[v-saisrao-msft]

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. 

[tharunkumarRTK · Accepted solution]

@ToastMaker 

From what I undestand you only need to unpivot the data, follow these steps.

• Load your Res_Register table into Power Query.
• Select the columns AF1 to AEP1 (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.
• Format the "Date" column:
  • If the column names AF1, AEP1, etc., are date codes, map or parse them into actual date values

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!