generate new column (from two table)

[wsindharta]

Hi, 

i have two column, one is machine column and the other date column. 

how to create the new table that have iteration between these two column ? i want to do it in power query or using m if possible. 

refer to screen shot below for explanation.

thanks in advance for help 

[wsindharta]

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

[danextian]

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.

[ajaybabuinturi]

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

[anmolmalviya05 · Accepted solution]

Hi , You can follow below steps in Power query to get the desired result:

First add a custom column in both the table which will be having same values for all the rows (for example: 1)

Then use the merge query option to merge both the table on the basis of the custom column. (refer screenshot below)

Use full outer join and expand the column from table 2.
At last remove the custom column and you will get desired result.

You can use below M-code (remember to create a custom column in table-2)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Table B", {"Custom"}, "Table B", JoinKind.FullOuter),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Date", "Custom"}, {"Table B.Date", "Table B.Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table B",{"Custom", "Table B.Custom"})
in
#"Removed Columns"

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@wsindharta

[ajaybabuinturi]

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.

[wsindharta]

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 ?  

[Shivu-2000]

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!