cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Replicating an Excel table with calculated cells

I'm not sure how you would do that, sorry.

Super User
Super User

Re: Replicating an Excel table with calculated cells

Hi @bestmbaman ,

 

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:

Print.png

 

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

 

 

 

 

 

 


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bestmbaman Member
Member

Re: Replicating an Excel table with calculated cells

@MFelix  Thanks for your detailed reply. I am sorry to get back later.

how did you import weeks table in the middle of editing Query?

Actually there was no weeks table but I produced it as a static table inside Power Query. Finally, I decided to use DAX formulas as you mentioned are so much straight but your proposed solution seems more useful in my case.

Would appreciate to correct my understanding that you did several input tables into the Contestant Table(my fact table)? if yes how? if not what are the Weeks table?

Thank you

Super User
Super User

Re: Replicating an Excel table with calculated cells

Hi @bestmbaman ,

 

One of the features in M Language is that you can refer to steps back or forward on your code, even more you can have different sources within the code and make them interact as you do with different tables if you check my coding there are two lines with source

 

    Source = Excel.Workbook(File.Contents("C:\Users\mfelix\Downloads\recordheights.xlsx"), null, 
true),
    Contestant_Table = Source{[Item="Contestant",Kind="Table"]}[Data],

    Weeks_Table = Source{[Item="Girls",Kind="Table"]}[Data]

The first line in this code is the excel file the other two refer to two tables on the file, they both are in the steps of the query so I get two datasources within the same query without the need of having additional tables on my model that I need to hide or not load upon refrresh.

 

It's a way of coding that allows you to not fill you model with lot's and lot's of tables that are used for support only.

 

Regards,

MFelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bestmbaman Member
Member

Re: Replicating an Excel table with calculated cells

@MFelix  Thanks for details and really sorry for my late reply!

I got confused when I looked closer at your solution! seems you have used a different source for all your actions.

I have reshared the main table that was the source of all data and it shouldn't be changed in anyway before calling in Power BI. Please see the Dropbox excel file again I shared.

https://www.dropbox.com/s/kpdnhlmxifz0332/recordheightsOriginal.xlsx?dl=0

My Weeks table is created in Power BI by "enter data" feature.

Moreover, did you used 3 different tables in your pbix file(1st Exel Workbook + two pther tables in the same workbook)?

And is it possible to call next tables(Contestant and Weeks tables) without writing the code in Advanced editor?

Also I was wondering what was the last line? I have a different variant in it (i.e. #"Removed Clumns1"? How I can change it and what is the impact on my overall data model?

Many thanks again...

bestmbaman Member
Member

Re: Replicating an Excel table with calculated cells

@MFelix 

Also I don't understand if it is possible to call the tables after cleaning previous data or I should call them one after eachother exactly?

 

Super User
Super User

Re: Replicating an Excel table with calculated cells

Hi @bestmbaman ,

 

Sorry for the late response. In my solution in order to prevent to have 3 querys (2 for the inputs and 1 for the merging) I made a single one with two different sources and then on a later step I merge the queries.

 

You can do it with different tables on your model and several queries no problem. I also created the weeks table in order to simplify my tough process you can do it in any source way you want it, from the excel file, a DB source or a direct input on the PBIX file.

 

The only steps that are important on this way of setting up things is the grouping, unpivot and the merging of the tables. the sources are multiple.

 

Regarding the advance coding you don't need it as refer prior you can make the merge from existing tables.

 

The last step is where imake the final calculation you can add steps after that removing or adding additional information.

 

Regards,

Mfelix


Regards

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
bestmbaman Member
Member

Re: Replicating an Excel table with calculated cells

@MFelixAs mentioned today I have uploaded the Actual source files(one Xls file and one .pbix file) in my dropbox folder for your consideration. Please consider these files as your source file in replacement of previous XLSX files that I shared previousely.

Please note that the format of my files are not XLSX but TXT or XLS, so you should work these files not XLSX since they are the dump of SAP transactions which is not designed to be saved as XLSX!

Please note that my PBI desktop version is September 2018 not July 2019!

Looking forward to hearing from you

bestmbaman

https://www.dropbox.com/s/v9x96fioch0tlul/ReplicateTopic.pbix?dl=0

https://www.dropbox.com/s/p5twxewsqof2lal/LPQInput.xls?dl=0

bestmbaman Member
Member

Re: Replicating an Excel table with calculated cells

Thanks again gurus for your replies! Please let me have your suggesttions

 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)