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
Anonymous
Not applicable

how to transpose data in powerbi

hi all,

i am loading the excel data by combining the sheets.this is how it look like after loading.

how can i get the table to display dates, product and the value correctly?

I tried using transpose & unpivot table but the results are wrong.

 

Can anyone help? thanks so much

 

A2.JPG

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

First, we replaced the blank rows/null rows to "Date" because Column1 will be our column header and null values are not allowed to be a header.

Then, we unpivot the column 2 to 6 because those will be the content of our columns.

Now that we have the Data for column headers and the values, we pivot the column 1 using the values we just unpivoted.

(When pivoting, do not aggregate).

Now we are close to your desired output. Finish it by removing the columns we don't need and change the data types according to you.

 

 

View solution in original post

19 REPLIES 19
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your source Excel files.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur 

This is the file, thanks so much 🙂

https://gofile.io/?c=1WhGvV

Hi,

You refer to my solution in this Blog article on my website.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

try this @Anonymous 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVFNC4JAFPwr4VlhP9q37jH7OARB0FE8WAmdKsQO/vt2FJe3sAUyDM9545uxrrPLo+sGmeWZf4Qsju2zkGbmh+4a+KntA9+8ezYfwZucOZ371/1zG1Ybz5WGjjxo4cE5zGRaX3kuIZBrDxavzLRJaf3Wc4K0BCiAVpgxfxUnI5aMWDJiyYglo9iJJyOL+/BRKRCNsEcqvVAtAjsBXjk0aMq0HtGcCCqkLgHuh/9uOYjQH02b8s89exhCb3W4TM3nBb2Oq7OsOsuqs6y6MB/BI6eoOtxnlr8Non0PzRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Data.Column1 = _t, Data.Column2 = _t, Data.Column3 = _t, Data.Column4 = _t, Data.Column5 = _t, Data.Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"","Date",Replacer.ReplaceValue,{"Data.Column1"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Name", "Data.Column1"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Data.Column1]), "Data.Column1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Name", "Attribute"})
in
    #"Removed Columns"

pbix is here.

Anonymous
Not applicable

@mussaenda 

got the desired results.

I am new to power bi and dont' quite understand how it actually works.

would you be able to elaborate more on the applied steps, especially on the date part.

 

Thank so much in advance

Hi @Anonymous ,

 

First, we replaced the blank rows/null rows to "Date" because Column1 will be our column header and null values are not allowed to be a header.

Then, we unpivot the column 2 to 6 because those will be the content of our columns.

Now that we have the Data for column headers and the values, we pivot the column 1 using the values we just unpivoted.

(When pivoting, do not aggregate).

Now we are close to your desired output. Finish it by removing the columns we don't need and change the data types according to you.

 

 

Anonymous
Not applicable

For "Then, we unpivot the column 2 to 6 because those will be the content of our columns."

Is this supposed to be what it look like?

@mussaenda 

1234.JPG

Yes, unpivot the columns that will serve as values.

 

Anonymous
Not applicable

@mussaenda only column 2-6?

No. As I said, unpivot the columns that will serve as your values.

If you have 5 columns that are the values for the column 1, unpivot them all.

If you have 30 columns that are the values for the column 1, unpivot them all.

If you have 50 columns that are the values for the column 1, unpivot them all.

Columns 2-6 are only based on the screenshot you provided.

 

Anonymous
Not applicable

Ahhh...understand.

ok, this is what i get after unpivot the content value of column

A.JPG

next, 1 pivot the column 1 using the values we just unpivoted,noted on this -do not aggregate

I got this?not sure where i donr wrongly.can help me

B.JPG

@mussaenda 

 

 

 

Upon pivoting, there is an advanced options. you click  that and choose do not aggregate

Anonymous
Not applicable

this is the results i got 

after unpivot columns for value

2.JPG

do not aggregate

1.JPG

i got this error, any advice? @mussaenda 
any explain on this part?

"Now that we have the Data for column headers and the values, we pivot the column 1 using the values we just unpivoted."

3.JPG

Go Back to the source applied steps.

Insert a step ->change the data types of the columns that will serve as values to text.

SteveCampbell
Memorable Member
Memorable Member

What I would do, is first get the data into one column. In the Advanced Editor, you can add the following step:

 

 

let
Source = //Your Source ,
#"Appended Query" = Table.Combine({ Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column1", "val"}),{"Name", "val"}), Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column2", "val"}),{"Name", "val"}), Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column3", "val"}),{"Name", "val"}), Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column4", "val"}),{"Name", "val"}), Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column5", "val"}),{"Name", "val"}), Table.SelectColumns(Table.RenameColumns(#"Source",{"Date.Column6", "val"}),{"Name", "val"}) })

in
#"Appended Query" 

 

Replace your data with the bit in red (see more on editing M code).

 

This should keep the product name in one column, and append all the other columns into one.

Output should look like this (my data is random and differnt to yours) -

Annotation 2019-08-24 134418.png

 

Next steps:

- Duplicate the column Val (Right click the column, and select duplicate column)

- In the new column Val - Copy, change the data type to "Date" (found in Data Type in the transform tab)

- Right click Val - Copy, and select "Replace Errors". Type in null in the box. This now leaves just the dates

- With Val - Copy, selected, apply a "Fill Down"

Annotation 2019-08-24 133242.png

- Now, filter the "Name" column to remove blanks

- You can rename the Val - Copy, to "Date"

 

Now, to get to your desired output, you would select Name, Click Pivot Column, Use val as the value column and in advanced settings change to "Don't Aggregate". However, I recommend you do not do this step. I would leave it in the format of three columns, Name, Date and val.

 

The reason, this is now a FACT table. You can create a date dimension table, and a product dimension table.

Then you can create a STAR schema. See the official guidance from Microsoft for more detail:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

 

 

 

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

Remember to spread knowledge in the community when you can! tu.png

 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



mussaenda
Super User
Super User

Hi @Anonymous ,

 

is this your desired output?

2019_08_24_16_53_06_Untitled_Power_Query_Editor.png

Anonymous
Not applicable

@mussaenda 

yes yes, may i know to achieve that?

SteveCampbell
Memorable Member
Memorable Member

What is the desired output?



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Anonymous
Not applicable

@SteveCampbell 

something like this.
Column A - date.

However I have another problem that is seen below.

Some product are in 2015 but no longer in 2016.

Any advice?

results.JPG

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.