Forum Discussion
Replicating an Excel table with calculated cells
Super UserHi Anonymous ,
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
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...
- MFelix
Hi Anonymous ,
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
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
Thanks again gurus for your replies! Please let me have your suggesttions
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?