Replicating an Excel table with calculated cells

Hi Anonymous ,

Just a few question I have based on the spreadsheet you provided that I cannot be sure about how the connection is made between the data:

• Is the week value per each girl being filled in any of your data? 
• In your file you have the total is based on a calculation that I assume is not totally exact see image below:

As you can see in the image taken from your excel file the Total sum all the weeks but also the columns N and Q believe this is not exact you only want to sum the weeks for the round up calculation. Is my assumption correct?

I also must tell you that based on the calculation you want to achieve it's better to do it in DAX measures since they can be reutilized and don't height on your model.

However it's possible to do it in M Language you need to do several steps and go back and forth on the steps but see the details below:

• Assuming you have two tables one with:
  • Contestant | Canabi | SLSQ (values are the ones show above)
  • Contestant | Girl | Week (values are the Weeks)
• Group Table Contestant by Contestant colum
  • Canabi = Sum
  • SLSQ = Sum
•  Pivot Table Weeks
  • Pivot should be done by column girl with values of week
• Group Table Weeks By contestant (this step should refer to the Table week step and not the Pivot step
• Merge the Pivot Table Step with the Group Table week step
  • This merge must be done by contestant column
• Expand the column with total weeks value
• Unpivot the Previous table
  • Get a new result with:
    • Contestant | Total Week  | Girl | Week
• Merge Unpivot table with Contestant Group table
  • This merge must be done by contestant column
• Expand SLSQ and Canabi Columns
• Add two new colums with the following code:
  • Number.Round([Week]/[Total Week]*[Canabi])
  • Number.Round([Week]/[Total Week]*[SLSQ])

The final outcome is the pivot column you need.

Check the code below and the PBIX file attach.

let
    Source = Excel.Workbook(File.Contents("C:\Users\mfelix\Downloads\recordheights.xlsx"), null, true),
    Contestant_Table = Source{[Item="Contestant",Kind="Table"]}[Data],
    Custom1 = Source{[Item="Contestant",Kind="Table"]}[Data],
    Custom2 = Source{[Item="Contestant",Kind="Table"]}[Data],
    Contestant_format = Table.TransformColumnTypes(Custom2,{{"Contestant", type text}, {"Canabi", Int64.Type}, {"SLSQ", Int64.Type}}),
    Contestant_Group = Table.Group(Contestant_format, {"Contestant"}, {{"Canabi", each List.Sum([Canabi]), type number}, {"SLSQ", each List.Sum([SLSQ]), type number}}),
    Weeks_Table = Source{[Item="Girls",Kind="Table"]}[Data],
    Weeks_Format = Table.TransformColumnTypes(Weeks_Table,{{"Contestant", type text}, {"Girl", type text}, {"Week", Int64.Type}}),
    Weeks_Pivot = Table.Pivot(Weeks_Format, List.Distinct(Weeks_Format[Girl]), "Girl", "Week", List.Sum),
    Weeks_Group = Table.Group(Weeks_Format, {"Contestant"}, {{"Total Week", each List.Sum([Week]), type number}}),
    Weeks_Merge_Total = Table.NestedJoin(Weeks_Pivot, {"Contestant"}, Weeks_Group, {"Contestant"}, "Total", JoinKind.LeftOuter),
    Weeks_Total_Expand = Table.ExpandTableColumn(Weeks_Merge_Total, "Total", {"Total Week"}, {"Total Week"}),
    Weeks_Unpivot = Table.UnpivotOtherColumns(Weeks_Total_Expand, {"Contestant", "Total Week"}, "Girl", "Week"),
    Weeks_Contest_Merge = Table.NestedJoin(Weeks_Unpivot, {"Contestant"}, Contestant_Group, {"Contestant"}, "SQLCANABIS", JoinKind.LeftOuter),
    Expand_Contestant_Columns = Table.ExpandTableColumn(Weeks_Contest_Merge, "SQLCANABIS", {"Canabi", "SLSQ"}, {"Canabi", "SLSQ"}),
    Canabi_Total_Column = Table.AddColumn(Expand_Contestant_Columns, "Total Canabi", each Number.Round([Week]/[Total Week]*[Canabi])),
    SLSQ_Total_Column = Table.AddColumn(Expand_Contestant_Columns, "Total Canabi", each Number.Round([Week]/[Total Week]*[SLSQ]))
in
    SLSQ_Total_Colum

Check PBIX file and excel file attached.

Regards,

MFelix