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,

 

You may download my PBI file from here.

 

Hope this helps.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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'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"
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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

         

        Kind Regards,

         

        Joseph