cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clout
Helper III
Helper III

Keep the transformation of data for another data sets

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.

1 ACCEPTED SOLUTION
Jimmy801
Super User III
Super User III

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

View solution in original post

2 REPLIES 2
Clout
Helper III
Helper III

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
Super User III
Super User III

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors