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

[Anonymous]

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

[PattemManohar]

@Anonymous 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)

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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"

[mkr4000]

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,

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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

Kind Regards,

Joseph

[Ashish_Mathur]

You are welcome.