## Desktop

Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-06-2018

# 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

Accepted Solutions
Super User
Posts: 3,934
Registered: ‎01-14-2017

## 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"```

All Replies
Super User
Posts: 3,934
Registered: ‎01-14-2017

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

Hi,

Hope this helps.

Frequent Visitor
Posts: 3
Registered: ‎12-06-2018

## 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
Posts: 3,934
Registered: ‎01-14-2017

## 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
Posts: 3
Registered: ‎12-06-2018

## 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
Posts: 3,934
Registered: ‎01-14-2017

You are welcome.