Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Osama14
Frequent Visitor

Table transformation correct format

Hello, 
I am quite new to the Power Bi and in need of help on how to properly prepare the data before loading

trying to transforming a dataset having ( 35 columns ) having value of each column by mintue using power query but cannot seem to find the correct way,  

 

The Data is reading from different meters and some categories are having 3 attributes,

The dataset is having a column for date, row for electricity and under it should be 3 rows for each meter sensor, the same applies to production and data flow see the image 

Table Screenshot.PNG

 

also another example would be Machie 1 having 3 attributes ( Electric consumption, Temp, Returning Temp) 

 

What will be the best way to transform the data to have a column for the 1st row? so it should be as the following: 

 

Date     Attribute    Meter 1 Meter 2 Meter 3 West 1 West 2 
1-1-21  Electrictiy    56        55            77          null      null 

1-1-21  Production   null        null          null        55       22             

 

After doing transpose and filling down the data and transpose again i got the below, but still not the structure that i want so i can get the value from each machine or category but it self if needed.  

 

Osama14_0-1630767142513.png

 

 Also will this way i will be able to load and do the visualization correctly ?    

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Osama14 ,

You can try this query:

let
    Source = Excel.Workbook(File.Contents("xxx\xx.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filled Up" = Table.FillUp(Sheet1_Sheet,{"Column1"}),
    #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Filled Up",{"Column1"})),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Attribute"}}),
    Custom1 = Table.AddColumn(#"Removed Other Columns","New",each #"Renamed Columns"),
    #"Expanded New" = Table.ExpandTableColumn(Custom1, "New", {"Attribute", "Column2", "Column3"}, {"New.Attribute", "New.Column2", "New.Column3"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded New", List.Distinct(#"Expanded New"[New.Column2]), "New.Column2", "New.Column3", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"New.Attribute", "Attribute"}, {"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Attribute", type text}, {"Meter 1", Int64.Type}, {"Meter 2", Int64.Type}, {"Meter 3", Int64.Type}, {"West 1", Int64.Type}, {"West 2", type number}, {"East 1", Int64.Type}, {"East 2", type number}})
in
    #"Changed Type"

vyingjl_0-1631174791811.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Osama14 ,

You can try this query:

let
    Source = Excel.Workbook(File.Contents("xxx\xx.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filled Up" = Table.FillUp(Sheet1_Sheet,{"Column1"}),
    #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Filled Up",{"Column1"})),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Attribute"}}),
    Custom1 = Table.AddColumn(#"Removed Other Columns","New",each #"Renamed Columns"),
    #"Expanded New" = Table.ExpandTableColumn(Custom1, "New", {"Attribute", "Column2", "Column3"}, {"New.Attribute", "New.Column2", "New.Column3"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded New", List.Distinct(#"Expanded New"[New.Column2]), "New.Column2", "New.Column3", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"New.Attribute", "Attribute"}, {"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Attribute", type text}, {"Meter 1", Int64.Type}, {"Meter 2", Int64.Type}, {"Meter 3", Int64.Type}, {"West 1", Int64.Type}, {"West 2", type number}, {"East 1", Int64.Type}, {"East 2", type number}})
in
    #"Changed Type"

vyingjl_0-1631174791811.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

watkinnc
Super User
Super User

I would first make a new query to get the current column names into a list, then just Promote headers, then select your new Column1, and then click "Unpivot other columns". Then you can rename the columns at once, using your list of columns as the second parameter.

 

That's it!

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
ImkeF
Super User
Super User

Hello @Osama14 ,

you can unpivot the top 2 rows at a time using my function over here: Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel – The BIccountant
Then "pivot back" with the column containing the meter names.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors