Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
@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)
Proud to be a PBI Community Champion
Hi,
You may download my PBI file from here.
Hope this helps.
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
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"
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,
Hi,
Share some data and show the expected result.
Thank you @Ashish_Mathur this works as required. I look forward to applying it to a larger dataset.
Kind Regards,
Joseph
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |