Reply
Frequent Visitor
Posts: 3
Registered: ‎12-06-2018
Accepted Solution

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,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2),
    #"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"

View solution in original post


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,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

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

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

Hi @Ashish_Mathur,

 

Thank you for your help.

 

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,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2),
    #"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

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

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

You are welcome.