Keep the transformation of data for another data sets

[Clout]

Hello Im new to Power BI and general new in databases. I absolved just an Udemy course this week and Im now deepening my practice.

However, I have to make for my company a dashboard (Bachelor Thesis) for a specific plant which is located in another area. The existing data consists of only one table in Access. So I have to do big transformations: removing columns which are not needed, rename columns, splitting the columns in another tables, create support tables and connect them. Also I have to do calculations respectively specific measures for the key performance indicators. And of the top of it visualise it.

The next requierement is the transferability of my work (transformation, modelling, visualisation, measures etc.) to another data set with the same columns/structure from another area (names are mostly identical but here and there are extra columns which I dont need). So I want to simply put my data in and the working steps I did with the former data, will be transfered to the new data. It would be also nice that Im able to chose prior which columns I will keep and the rest should be deleted. The first ten rows are only consist of the date and the remaining rows of the another columns are empty. The data of the free space is individual to each plant, so it would be a nice to have to detect the empty rows of my relevant data and clear every row till the first data aviable.

It should be possible isnt it? When I go in Power Query to the "Enhanced Editor" I can see all steps in "M" and the transformation bases mostly on the column names and tables, so it would be theoretically possible to just change the file path for the simple transformation steps. But is it possible to detect for example automatically the first empty rows of my data and delete it?

So and prior of this I want to set a new database for my company where I can put every Access data in it and recall it in Power BI, so the user just have to chose the Plant which he wants to see and the Dashboard adapts to the new plant data. This would be theoretically possible with MySQL right?

I would be very thankfull when someone can help me out and can hand me out some sources which I can work with.

[Clout]

Allright thank you for your answer!

So I have just to keep the data transformation in such way, that it doesn't interfere with the another data? For example when I rename a column name to another name and the new data doesn't include the primarily column name, I get a error because Power Query doesn't find the name. So it is more safe to change the column names in the database I think.

[Jimmy801 · Accepted solution]

Hello @Clout 

this are a lot of question. If you find a logic to transform your data, in PQ you can do it. About your question of keeping rows you could create a list of column names and then keep only them, so you are able to build a manual list and PQ keeps them automatically, so you don't need to dig into the code. About your question regarding removing empty rows at the beginning you can check my example here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVwU6kZRYVl4BZKYkliWBGUX55sVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    RemoveFirstEmptyRowsInColumn1 = Table.RemoveFirstN(#"Changed Type", each [Column1]="")
in
    RemoveFirstEmptyRowsInColumn1

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy