Forum Discussion
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
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"
8 Replies
- Ashish_Mathur
Super User
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
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"
- PattemManohar
Community Champion
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)
