cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate New Table - "For Loop" - Repeat Table A based on Table B

Hello,

I'd like to create a new table (Table C) based upon contents of Table A and Table B. I imagine as much what I am trying to achieve is a For Loop.

Dummy data can be found below:

Table A has the follow contents:

```TEAM,SUBTEAM
Team 1,SubTeam1A
Team 1,SubTeam1B
Team 1,SubTeam1C
Team 2,SubTeam2A
Team 2,SubTeam2B
Team 2,SubTeam2C```

And Table 2 is as simple as:

```MTH YEAR,YEAR,QTR
1/10/18,2018,2
1/11/18,2018,2
1/12/18,2018,2```

My enquiry is pretty straight forward - I think.

I want to calculate Table C to be Table A repeated based upon Table B[MTH YEAR]. Example output would look like the below:

```MTH YEAR,TEAM,SUBTEAM
1/10/18,Team 1,SubTeam1A
1/10/18,Team 1,SubTeam1B
1/10/18,Team 1,SubTeam1C
1/10/18,Team 2,SubTeam2A
1/10/18,Team 2,SubTeam2B
1/10/18,Team 2,SubTeam2C
1/11/18,Team 1,SubTeam1A
1/11/18,Team 1,SubTeam1B
1/11/18,Team 1,SubTeam1C
1/11/18,Team 2,SubTeam2A
1/11/18,Team 2,SubTeam2B
1/11/18,Team 2,SubTeam2C
1/12/18,Team 1,SubTeam1A
1/12/18,Team 1,SubTeam1B
1/12/18,Team 1,SubTeam1C
1/12/18,Team 2,SubTeam2A
1/12/18,Team 2,SubTeam2B
1/12/18,Team 2,SubTeam2C```

The query would allow me to create additional calculated columns that will allow me to move a number of operations out of excel.

Kind Regards,

Joseph

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Hi,

Here's the M code

```let
Source = Table1,
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Mth Year"}, {"Mth Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Mth Year", "Team", "Sub team"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Reordered Columns", {{"Mth Year", type date}}, "en-IN"),
#"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Mth Year", Order.Ascending}, {"Team", Order.Ascending}, {"Sub team", Order.Ascending}})
in
#"Sorted Rows"```
8 REPLIES 8
Super User

Hi,

Hope this helps.

Frequent Visitor

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Unfortunately the link isn't working on my network - as it is blocked.

Is there any chance you can post the code required so I can try this myself?

Kind Regards,

Joseph

Super User

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Hi,

Here's the M code

```let
Source = Table1,
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Mth Year"}, {"Mth Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Mth Year", "Team", "Sub team"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Reordered Columns", {{"Mth Year", type date}}, "en-IN"),
#"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Mth Year", Order.Ascending}, {"Team", Order.Ascending}, {"Sub team", Order.Ascending}})
in
#"Sorted Rows"```
Frequent Visitor

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Thank you @Ashish_Mathur this works as required. I look forward to applying it to a larger dataset.

Kind Regards,

Joseph

Super User

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

You are welcome.

Occasional Visitor

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Hi Ashish_Mathur,

Forgive the question, but I'm new to M and I have failed to implement the code you provided. The table that is produced when I run the code is a complete replica of one table with an added column containing a null value. The one good thing is that the count of records duplicated is consistent with what I am trying to duplicate, but I don't see the values I need added. I'm trying to create this table to relate information.

If this is the syntax for the Table.AddColumn and Table.ExpandTableColumn functions:

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional...) as table

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as

table

then in your code the items you have are as follows and my questions are listed below:

```Source = Table1 #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Mth Year"}, {"Mth Year"}), ```

Question 1: Is your "Table1" the "Table A" the member first listed - making your "Table2" the "table 2" originally posted?

Question 2: Source = "Table1", so in table.addcolumn are we using Table1 as the "base table" and adding a column called Custom duplicating Table1 for each recod in your "Table2"?

Question 3: It seems like this might just be renaming the column "Custom", but I think I'm missing something. Would you mind explaining?

I know this response/question is a bit intense. Thanks for your considertaion.

Best,

Super User

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

Hi,

Share some data and show the expected result.

Highlighted
Super User

## Re: Calculate New Table - "For Loop" - Repeat Table A based on Table B

@JosephJack If you want to achieve the same output in DAX, then please try as below

```Test272Out =
VAR _MonthYear = VALUES(Test272CrossJoinT2[MonthYear])
RETURN CROSSJOIN(Test272CrossJoinT1,_MonthYear)```

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

Proud to be a Datanaut !