generate new column (from two table)

Hi wsindharta 

This can be solved by 2 methods:

Steps in Power Query

1. Load both tables (Table 1 and Table 2):

   • Load the Machine column as Table1.
   • Load the Date column as Table2.

2. Go to the Home tab:

   • Select the Machine table (Table1) first.
   • Click on Home > Merge Queries.

3. Perform a Cartesian Product:

   • In the Merge Queries window:
     • Select Table2 in the second dropdown.
     • Do not select any columns in either table; just click OK.

4. Expand the Combined Table:

   • After merging:
     • Click on the small expand icon in the new column.
     • Select Date from Table2 to expand the dates.

5. Rename and Arrange Columns:

   • Rename the columns to Machine and Date.
   • Ensure the columns are arranged in the desired order.

6. Load the New Table:

   • Click Close & Load to create the new table in Excel.

or 
M- CODE

let
// Load Table1
Table1 = Table.FromRows({{"A"}, {"B"}, {"C"}, {"D"}}, {"Machine"}),

// Load Table2
Table2 = Table.FromRows({{"1/1/2025"}, {"2/2/2025"}, {"3/2/2025"}}, {"Date"}),

// Add a Custom Column with Table2 for Cartesian Product
AddCustomColumn = Table.AddColumn(Table1, "Dates", each Table2),

// Expand the Nested Table
ExpandDates = Table.ExpandTableColumn(AddCustomColumn, "Dates", {"Date"}),

// Convert Date Column to Date Type (Optional)
FormatDates = Table.TransformColumnTypes(ExpandDates, {{"Date", type date}})
in
FormatDates

THE OUTPUT WILL LOOK LIKE THIS

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

hi shshsh 

thank you for your prompt response. 

for method one : if i don't choose any column from the two table merging, "OK" button is grey out, so i can't move beyond that

for methos two : the list of machine and date is very long, can you help me change the script, to take from column name : workstation (table machine) and column date1 from table date ?  

Hi wsindharta ,

I have created reference table for Table1, if you want to get the results without reference tabke you can do the below process directly in the Table1.

Here is the M-code
let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "DateTable", each Table2),
#"Expanded DateTable" = Table.ExpandTableColumn(#"Added Custom", "DateTable", {"date"}, {"DateTable.date"})
in
#"Expanded DateTable"

Results

If the solutions helps, marking it 'Accept as Solution' so if anyone facing same questions it may helps.

Thank you.

Hi wsindharta ,

I have used reference table to get the results, if you want get the results directly in the Table1. You can use the below process.
Here is the M-code

let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "DateTable", each Table2),
#"Expanded DateTable" = Table.ExpandTableColumn(#"Added Custom", "DateTable", {"date"}, {"DateTable.date"})
in
#"Expanded DateTable"

Results:

If the solution helps, please mark it as "Accepted Solution" so it may help others if ther facing same questions/issues.

Thanks

Hi wsindharta 

In Power Query, the easiest method is to create a custom column in Table1 that references Table2. This will return Table2 for each row in Table1, which you can then expand as needed.

Hi Everyone, thank you for your reply i have mark the answer that work thanks