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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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"

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors