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
David283
Helper I
Helper I

Error pivoting column.

Hello. New to Power Query and Power BI.  Trying to learn how to pivot and unpivot to arange my columns in a way that will display well.  Google has gotten me far, but I could use some help with this corner I am in.

 

I had a table with employee names and numbers aranged in 1 row per date, 5 day shift positions and 5 night shift.  Name and numbers for each , so 20 columns.  I want to arange that in a new table with one name and number per row, with an extra column for the value of day or night shift.

 

I was able to get that far with unpivot and renaming values.  But now i need to pivot to get the name next to the number again.  See below.

 

David283_0-1661033903578.png

 

So I added the custom column "PivotType" and calculated if the value was a name or number.

 

= Table.AddColumn(#"Replaced Value1", "PivotType", each if Text.Length([Value]) <= 5 then "Number" else "Name")

 

Then i did the pivot on that custom column, targeting the "Value" column for the value.

 

David283_1-1661034083883.png

 

But the result is a repeting error = Expression.Error: There weren't enough elements in the enumeration to complete the operation.

 

Here is my full code =

 

let
    Source = CombinedAttendance,
    #"Filtered Rows" = Table.SelectRows(Source, each Date.IsInPreviousNDays([cr242_dailylogdate], 1)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"cr242_dailylogfilename", "cr242_dailyloglink", "cr242_dailyfleetloc", "cr242_filespid", "cr242_vesselservice", "cr242_vesselname", "cr242_dailylogdate", "cr242_dailylognumber"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "WatchType"}}),
    #"Replaced Value" = Table.ReplaceValue( #"Renamed Columns" ,each [WatchType],each if Text.Contains([WatchType], "back") then "Backwatch" else [WatchType],Replacer.ReplaceValue,{"WatchType"}),
    #"Replaced Value1" = Table.ReplaceValue( #"Replaced Value" ,each [WatchType],each if Text.Contains([WatchType], "front") then "Frontwatch" else [WatchType],Replacer.ReplaceValue,{"WatchType"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "PivotType", each if Text.Length([Value]) <= 5 then "Number" else "Name"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[PivotType]), "PivotType", "Value")
in
    #"Pivoted Column"

 

 

Can anyone assist?

1 ACCEPTED SOLUTION

Q. And was it important to change the type to number for the value column? I wont be able to do that with my data set - Not needed.

When you are pivoting a table, you need to provide a column on the basis of which it can evenly distribute columns (most of time, you will have this kind of column in your data. But you don't have one such column in your data). Since you wanted to have two columns in pivot, hence I had to provide an identifier which provides a sequence 1,1,2,2,3,3,4,4,5,5 and so on.

So first row contains two columns pertaining to 1,1

Second row for 2,2 and so on..

When you do this then you won't encounter the issue of enough elements in the enumeration.

Purpose of #"Added Index" and #"Divided Column" together is to provide this series.

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3tNQ3MjAyUtJRMjI1MQNSTkBsbmgIEvFLzE1VitXBqczSxNIQpKw0Nym1CJ9CMyNzQyLMMzQysiBonhuIaQlm4jMPpMzC1ICw+0AKjYkxzdDQwBSvaUaw0DMxNsBjHlyZmZEJknmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cs242_dailylogdate = _t, cs242_dailylognumber = _t, WatchType = _t, Value = _t, PivotType = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cs242_dailylogdate", type date}, {"cs242_dailylognumber", Int64.Type}, {"WatchType", type text}, {"Value", Int64.Type}, {"PivotType", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[PivotType]), "PivotType", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

Did as you asked, this is the result =

 

David283_0-1661067718792.png

 

 

Can you explain what you have done here and why it worked?    I see you added an index column, but it isnt refrenced in the pivot step, so I am not sure what its doing for us.

And was it important to change the type to number for the value column? I wont be able to do that with my data set.

Q. And was it important to change the type to number for the value column? I wont be able to do that with my data set - Not needed.

When you are pivoting a table, you need to provide a column on the basis of which it can evenly distribute columns (most of time, you will have this kind of column in your data. But you don't have one such column in your data). Since you wanted to have two columns in pivot, hence I had to provide an identifier which provides a sequence 1,1,2,2,3,3,4,4,5,5 and so on.

So first row contains two columns pertaining to 1,1

Second row for 2,2 and so on..

When you do this then you won't encounter the issue of enough elements in the enumeration.

Purpose of #"Added Index" and #"Divided Column" together is to provide this series.

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

Got this implemented in my report and its working great now, thank you!

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
Top Kudoed Authors