Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
@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.
@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 |
User | Count |
---|---|
134 | |
72 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |