Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
richardlrz
New Member

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 :

richardlrz_0-1681360064844.png

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

 

 

1 ACCEPTED SOLUTION

@amitchandak Thanks for the replay, in deed here is sample what I realy have:

 

LocationAttributeValueTimeStamp
LocationShiftDay10/10/2022
LocationSampleValue1.110/10/2022
LocationSampleNamePH10/10/2022
LocationOperationAlex10/10/2022
LocationShiftNight10/11/2022
LocationSampleValue2.110/11/2022
LocationOperationJoe10/11/2022
LocationNameBF10/11/2022
LocationShiftDay10/12/2022
LocationSampleNameNH310/12/2022
LocationOperationPhil10/12/2022
LocationNameR/C10/12/2022
LocationSampleValue5.510/13/2022
LocationSampleNamePH10/13/2022
LocationOperationAlex10/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:

TimeStampeShiftSampleValueSampleNameOperationName
10/10/2022Day1.1PHAlex 
10/11/2022Night2.1 JoeBF
10/12/2022Day NH3PhilR/C
10/13/2022 5.5PHAlex 

 

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)

 

TimeStampeShiftSampleValueSampleNameOperationName
10/10/2022Day1.1PHAlexNA
10/11/2022Night2.1PHJoeBF
10/12/2022Day2.1NH3PhilR/C
10/13/2022Day5.5PHAlexR/C

View solution in original post

3 REPLIES 3
richardlrz
New Member

@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_0-1681398029447.png

 

amitchandak
Super User
Super User

@richardlrz , I created a sample

amitchandak_0-1681366843715.png

 

 

After doing pivot with Max of Value, I am able to get data like

amitchandak_1-1681366875519.png

 

 

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"

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Thanks for the replay, in deed here is sample what I realy have:

 

LocationAttributeValueTimeStamp
LocationShiftDay10/10/2022
LocationSampleValue1.110/10/2022
LocationSampleNamePH10/10/2022
LocationOperationAlex10/10/2022
LocationShiftNight10/11/2022
LocationSampleValue2.110/11/2022
LocationOperationJoe10/11/2022
LocationNameBF10/11/2022
LocationShiftDay10/12/2022
LocationSampleNameNH310/12/2022
LocationOperationPhil10/12/2022
LocationNameR/C10/12/2022
LocationSampleValue5.510/13/2022
LocationSampleNamePH10/13/2022
LocationOperationAlex10/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:

TimeStampeShiftSampleValueSampleNameOperationName
10/10/2022Day1.1PHAlex 
10/11/2022Night2.1 JoeBF
10/12/2022Day NH3PhilR/C
10/13/2022 5.5PHAlex 

 

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)

 

TimeStampeShiftSampleValueSampleNameOperationName
10/10/2022Day1.1PHAlexNA
10/11/2022Night2.1PHJoeBF
10/12/2022Day2.1NH3PhilR/C
10/13/2022Day5.5PHAlexR/C

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.