How to pivot table

[richardlrz]

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

[richardlrz]

@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.

[amitchandak]

@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"

[richardlrz · Accepted 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