Forum Discussion
How to pivot table
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
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 |