Forum Discussion
How to pivot table
Hello I am pretty new for using poweBI desktop, I spent quit bit time try to just pivot a table from the datasouce, say I have source as :
I want to have a visual just make attribute as column and all value as row but without any aggregation , just plain as below:
Timestamp|Shift |Sample Name | Sample Value |Name |Operator
xxxxx xxxx xxxxxxx xxxxx xxxxx xxxxx here the xxx are value from column value and for those value missing for the attribute I will use previous value. it looks easy in SQL but not easy for the power BI, can some one help me out?
Thanks
amitchandak Thanks for the replay, in deed here is sample what I realy have:
Location Attribute Value TimeStamp Location Shift Day 10/10/2022 Location SampleValue 1.1 10/10/2022 Location SampleName PH 10/10/2022 Location Operation Alex 10/10/2022 Location Shift Night 10/11/2022 Location SampleValue 2.1 10/11/2022 Location Operation Joe 10/11/2022 Location Name BF 10/11/2022 Location Shift Day 10/12/2022 Location SampleName NH3 10/12/2022 Location Operation Phil 10/12/2022 Location Name R/C 10/12/2022 Location SampleValue 5.5 10/13/2022 Location SampleName PH 10/13/2022 Location Operation Alex 10/13/2022 Every time I only log the on value changed record, says I have 5 attributes, they are Shift, SampleValue, SampleName, Operation and Name 5 columns, I want pivot the table based on the Attributs on Value, I have successfully done this in the transform and get datasets like below:
TimeStampe Shift SampleValue SampleName Operation Name 10/10/2022 Day 1.1 PH Alex 10/11/2022 Night 2.1 Joe BF 10/12/2022 Day NH3 Phil R/C 10/13/2022 5.5 PH Alex For those blank cell I would like fill with previous record data and for the first row or couple row which has data can be found m I will use NA to fill those blank cells, so finally I will get :
The blank cell could happend on any column pivoted (say: Shift, SampleValue, SampleName, Operation and Name each row is based the timeStamp)
TimeStampe Shift SampleValue SampleName Operation Name 10/10/2022 Day 1.1 PH Alex NA 10/11/2022 Night 2.1 PH Joe BF 10/12/2022 Day 2.1 NH3 Phil R/C 10/13/2022 Day 5.5 PH Alex R/C
3 Replies
richardlrz , I created a sample
After doing pivot with Max of Value, I am able to get data like
The code you can use in blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slPTizJzM9T0lFyBGJDAxBhqA9ERgZGRkqxOihKnIDYL7VcISwxpzQVr0qwYaZQJUZ4DSsDGWaIpjQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Attrib = _t, Value = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Attrib", type text}, {"Value", type text}, {"Date", type date}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attrib]), "Attrib", "Value", List.Max)If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
in
#"Pivoted Column"amitchandak Thanks for the replay, in deed here is sample what I realy have:
Location Attribute Value TimeStamp Location Shift Day 10/10/2022 Location SampleValue 1.1 10/10/2022 Location SampleName PH 10/10/2022 Location Operation Alex 10/10/2022 Location Shift Night 10/11/2022 Location SampleValue 2.1 10/11/2022 Location Operation Joe 10/11/2022 Location Name BF 10/11/2022 Location Shift Day 10/12/2022 Location SampleName NH3 10/12/2022 Location Operation Phil 10/12/2022 Location Name R/C 10/12/2022 Location SampleValue 5.5 10/13/2022 Location SampleName PH 10/13/2022 Location Operation Alex 10/13/2022 Every time I only log the on value changed record, says I have 5 attributes, they are Shift, SampleValue, SampleName, Operation and Name 5 columns, I want pivot the table based on the Attributs on Value, I have successfully done this in the transform and get datasets like below:
TimeStampe Shift SampleValue SampleName Operation Name 10/10/2022 Day 1.1 PH Alex 10/11/2022 Night 2.1 Joe BF 10/12/2022 Day NH3 Phil R/C 10/13/2022 5.5 PH Alex For those blank cell I would like fill with previous record data and for the first row or couple row which has data can be found m I will use NA to fill those blank cells, so finally I will get :
The blank cell could happend on any column pivoted (say: Shift, SampleValue, SampleName, Operation and Name each row is based the timeStamp)
TimeStampe Shift SampleValue SampleName Operation Name 10/10/2022 Day 1.1 PH Alex NA 10/11/2022 Night 2.1 PH Joe BF 10/12/2022 Day 2.1 NH3 Phil R/C 10/13/2022 Day 5.5 PH Alex R/C
amitchandak indeed I have just archieve the filll the all null data by previous data in the transform section and select fill down but I am still struggle the fill the blank cells with "NA" which don't have any available data cell in the previous timestampe.
