RE: Add custom calculated rows/columns using Power Query Editor. Load all the tables in a single sheet using Power Query Editor. Reuse Power Querystep

[rajanshre]

Hello everyone,

I have raw data that I process on a daily basis, and I need to clean the data, remove unwanted/unnecessary rows and columns, and add custom(calculated) rows and columns so that I could have a single page report, possibly. I did the best I could do using the power query editor and simple 'M' code(these are the only tools that I thought would be useful for data cleaning and processing) to be able to remove some rows and columns. But when I close and load the data in the power query editor, it creates multiple worksheets for each table except I wanted to load everything in a single sheet so that the final report would look clean, organized, and more readable for everyone. I would also like to be able to add custom rows where I can have a calculated number and another custom row with title 'Miscellaneous' in which we can manually enter data if necessary.
I would also need to be able to reuse power query editor steps and 'M' code on a daily basis because the report that is processed everyday pretty much has the same format and only the data will be different.

Thank you,
Rajan

 

[ExcelToDAX]

Can you include some sample data? It is difficult to help you without any sample data.

 

[rajanshre]

Can you include some sample data? It is difficult to help you without any sample data.

These are some sample pictures of what I got after using Power Query Editor and M code but these are all in different sheets and I wanted them to be better organized in a single worksheet. I extracted these data to Power query editor from a html file that is processed on a daily basis. I basically remove unwanted columns and use M code editor to be able to remove unwanted custom rows.

 

[ExcelToDAX]

Okay so which columns are irrelevant? From the looks of the screenshots, you would want 1 column that combines Description/Category/Product. You would then want a column named "Attribute" which would include Volume,Amount,Count, and then a third column named Value that has the value for each Volume,Amount,Count. If you could better explain which things are necessary/irrelevant I can better assist you.

 

[rajanshre]

Okay so which columns are irrelevant

Okay so which columns are irrelevant? From the looks of the screenshots, you would want 1 column that combines Description/Category/Product. You would then want a column named "Attribute" which would include Volume,Amount,Count, and then a third column named Value that has the value for each Volume,Amount,Count. If you could better explain which things are necessary/irrelevant I can better assist you.

These are all relevant and we need all these because I already deleted unwanted/unnecessary rows and columns from the source data which is an HTML document using power query and M code editor to remove custom rows, and loaded to these excel sheet. But the problem is when you 'load to', it creates multiple/individual sheet rather than loading to one single page sheet which is my goal. I tried to upload actual HTML data/document here so that you would have a better idea of what I have been trying to achieve except there is no option to upload HTML document here, only images or links.

 

[ExcelToDAX]

okay so if you want one table, you will need similar columns names like I said. How can we categorize this to get them into 1 table?

 

[rajanshre]

Okay so which columns are irrelevant? From the looks of the screenshots, you would want 1 column that combines Description/Category/Product. You would then want a column named "Attribute" which would include Volume,Amount,Count, and then a third column named Value that has the value for each Volume,Amount,Count. If you could better explain which things are necessary/irrelevant I can better assist you.

So this is where I started. I extracted the data using HTML file and applied required steps on the right. This is just a picture of one table. I have used similar steps on rest of the tables to remove/delete unwanted rows and columns and used M code editor to remove custom rows because you can easily remove custom columns but you need to use special code in M code editor to be able to remove/delete custom rows like I did.

 

[rajanshre]

View attachment 124106
So this is where I started. I extracted the data using HTML file and applied required steps on the right. This is just a picture of one table. I have used similar steps on rest of the tables to remove/delete unwanted rows and columns and used M code editor to remove custom rows because you can easily remove custom columns but you need to use special code in M code editor to be able to remove/delete custom rows like I did.

 

[rajanshre]

okay so if you want one table, you will need similar columns names like I said. How can we categorize this to get them into 1 table?

That's what I have been trying to figure out how to combine all these table in a single table OR any single page without having to waste too much time or too much work.

 

[ExcelToDAX]

You can append all of these queries into 1 query. You need to have similar columns headers though, because if not your data will look wonky.