Error pivoting column.

[David283]

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.

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.

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?

[Vijay_A_Verma]

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

[David283]

Did as you asked, this is the result =

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.

[Vijay_A_Verma · 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

[David283]

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