cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mellyVK Member
Member

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

Accepted Solutions
mussaenda Senior Member
Senior Member

Re: how to transpose data in powerbi

Hi @mellyVK ,

 

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.

 

 

19 REPLIES 19
SteveCampbell Established Member
Established Member

Re: how to transpose data in powerbi

What is the desired output?

mellyVK Member
Member

Re: how to transpose data in powerbi

@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

mussaenda Senior Member
Senior Member

Re: how to transpose data in powerbi

Hi @mellyVK ,

 

is this your desired output?

2019_08_24_16_53_06_Untitled_Power_Query_Editor.png

mellyVK Member
Member

Re: how to transpose data in powerbi

@mussaenda 

yes yes, may i know to achieve that?

SteveCampbell Established Member
Established Member

Re: how to transpose data in powerbi

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

 

Super User
Super User

Re: how to transpose data in powerbi

Hi,

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

mellyVK Member
Member

Re: how to transpose data in powerbi

@Ashish_Mathur 

This is the file, thanks so much Smiley Happy

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

mussaenda Senior Member
Senior Member

Re: how to transpose data in powerbi

try this @mellyVK 

 

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.

mellyVK Member
Member

Re: how to transpose data in powerbi

@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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 126 members 1,540 guests
Please welcome our newest community members: