Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:   Tab...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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"
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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's avatar
mkr4000
New Member
7 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    Share some data and show the expected result.