Table Transpose in Visual

[jkhan]

Hi All,

Require help to transpose the below table 

like below

The idea is need to make Machine1,Machine2,Machine3 as column and remaining as rows.

Thanks & Best Regards

Jamsher

[jkhan]

Hi @PaulDBrown , 
Because of Date is different. 

Thanks & Regards

Jamsher

[PaulDBrown]

So which date/time needs to be shown? Machine 1 also has several date/time values

[jkhan]

@PaulDBrown ,

We can create machine2_1 and machine2_2 colum it we have mutliple records for machine in same date 

Thanks & Regards

[PaulDBrown]

Sorry, I'm not following. You mean you want to show all the values for each date/time for each machine?

[jkhan]

Yes Mr. @PaulDBrown 

[PaulDBrown]

Can you post a link to a PBIX file with the sample data? the link you posted is to a file in which the data is unusable:

[jkhan]

Hi @PaulDBrown 

Uploading the pbix file. 

Download URL : CPU_PRODUCTION -New.pbix

Thanks & Regards

Jamsher

[PaulDBrown]

Why does machine 2 have two columns?

[Bhu1singh]

Hi,
You can transpose the table in Power Query,

1. Go to power query Editor>> right click on Machin_number column and say unPivot other columns

2. Then again select Machin_number column and in Tranformation menu select Pivot option.

You should get the table in desired format.

Thanks

Bhuvan

[jkhan]

Hi @Bhu1singh ,

I did the steps as you mentioned. But getting below result. I can attached sample pbix file to download. 

file download : CPU_PRODUCTION.pbix

Thanks & Regards

Jamsher

[Bhu1singh]

Hi @jkhan 

I dont have access to your data, can you past below steps in a blank query (data source) in power query editor. This is what I am suggesting to implement.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMSzVU0lECY31DfSMDIyMgMzGlWClWB64CJGRkZAxWYwRXU5yGrAYkawJWYYwwBUOFKViFCYqKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine_Number = _t, Prod1 = _t, Prod2 = _t, Prod3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine_Number", type text}, {"Prod1", Int64.Type}, {"Prod2", type date}, {"Prod3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Machine_Number"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Machine_Number", "Attribute"}, {{"Unique_Value", each List.Max([Value]), type any}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Machine_Number]), "Machine_Number", "Unique_Value")
in
#"Pivoted Column"

Thanks

Bhuvan

[jkhan]

@Bhu1singh 

I already uploaded the powerbi pbix file, Please download from below link. 

file download : CPU_PRODUCTION.pbix

Thanks & Regards

Jamsher

[jkhan]

Dear All,

Any hint please. 

Thanks & Regards

Jamsher