powerbi - Dataflow forcing a List to be a Table - Stack Overflow

admin管理员组

文章数量:1123377

I have a pretty complicated date table I am building using a function and holiday list. It works in power bi desktop. My holiday list is stored as a list in power query, and the function handles it properly.

However, when I put the same tables/lists/functions in the dataflow in the service, I get an error when refreshing. "Expression.Error: We cannot convert a value of type List to type Table.".

It's weird, because in the dataflow editor, as I step through the query, there are no errors, and I see the function called and expected output. However, after saving, closing, and refreshing I get that error. And then, when I go back into the editor, my HolidayList is no longer a list and is back to a table.

Calendar Table here:

let
  Today = Date.From( DateTime.LocalNow() ),
  #"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
  #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
  #"Insert Date Integer" = Table.AddColumn(#"Converted to Table", "Date Integer", each Number.From( Date.ToText( [Date], "yyyyMMdd" ) ), Int64.Type),
  #"Insert Year" = Table.AddColumn(#"Insert Date Integer", "Year", each Date.Year([Date]), Int64.Type),
  // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the Current period.
  #"Add Year Default" = Table.AddColumn(#"Insert Year", "Year Default", each if Date.Year( Today ) = [Year] then "Current" else Text.From( [Year] ), type text),
  #"Insert YYYY-MM" = Table.AddColumn(#"Add Year Default", "YYYY-MM", each Date.ToText([Date], "yyyy-MM"), type text),
  #"Insert Month-Year" = Table.AddColumn(#"Insert YYYY-MM", "Month-Year", each Date.ToText([Date], "MMM yyyy"), type text),
  #"Insert Month Number" = Table.AddColumn(#"Insert Month-Year", "Month Of Year", each Date.Month([Date]), Int64.Type),
  #"Insert Month Name" = Table.AddColumn(#"Insert Month Number", "Month Name", each Date.MonthName([Date], "EN-us"), type text),
  #"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Date.ToText([Date], "MMM", "EN-us"), type text),
  // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the current period.
  #"Add Month Name Default" = Table.AddColumn(#"Insert Month Name Short", "Month Name Default", each if Date.Month( Today ) = [Month Of Year] then "Current" else [Month Name], type text),
  #"Insert Start of Month" = Table.AddColumn(#"Add Month Name Default", "Start of Month", each Date.StartOfMonth([Date]), type date),
  #"Inserted End of Month" = Table.AddColumn(#"Insert Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
  #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
  #"Add ISO Week" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Days in Month", "Week of Year", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Sunday ) ),
YearCurrThursday = Date.Year( CurrentThursday ),
FirstThursdayOfYear = Date.AddDays(#date( YearCurrThursday,1,7),- Date.DayOfWeek(#date(YearCurrThursday,1,1), Day.Friday) ),
ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear) / 7 + 1
in ISO_Week), {{"Week of Year", Int64.Type}}),
  #"Add ISO Year" = Table.AddColumn(#"Add ISO Week", "ISO Year", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday )
in YearCurrThursday, Int64.Type),
  #"Insert Start of Week" = Table.AddColumn(#"Add ISO Year", "Start of Week", each Date.StartOfWeek([Date], Day.Sunday), type date),
  #"Insert Quarter Number" = Table.AddColumn(#"Insert Start of Week", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
  #"Added Quarter" = Table.AddColumn(#"Insert Quarter Number", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Date]) ), type text),
  #"Add Year-Quarter" = Table.AddColumn(#"Added Quarter", "Year-Quarter", each Text.From( Date.Year([Date]) ) & "-Q" & Text.From( Date.QuarterOfYear([Date]) ), type text),
  #"Insert Day Name" = Table.AddColumn(#"Add Year-Quarter", "Day Name", each Date.DayOfWeekName([Date], "EN-us"), type text),
  #"Insert Day Name Short" = Table.AddColumn(#"Insert Day Name", "Day Name Short", each Date.ToText([Date], "ddd", "EN-us"), type text),
  #"Insert Day of Month Number" = Table.AddColumn(#"Insert Day Name Short", "Day of Month Number", each Date.Day([Date]), Int64.Type),
  #"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month Number", "Day of Week Number", each Date.DayOfWeek([Date])+1, Int64.Type),
  #"Insert Day of Year" = Table.AddColumn(#"Insert Day of Week", "Day of Year Number", each Date.DayOfYear([Date]), Int64.Type),
  #"Add Day Offset" = Table.TransformColumnTypes(Table.AddColumn(#"Insert Day of Year", "Day Offset", each Number.From( [Date] - Date.From( Today ) )), {{"Day Offset", Int64.Type}}),
  #"Add Week Offset" = Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.Sunday) )- Number.From( Date.StartOfWeek( Today, Day.Sunday ) ) ) / 7, Int64.Type),
  #"Add Month Offset" = Table.AddColumn(#"Add Week Offset", "Month Offset", each ([Year] - Date.Year( Today ) ) *12 + ( [Month Of Year] - Date.Month( Today ) ), Int64.Type),
  #"Add Quarter Offset" = Table.AddColumn(#"Add Month Offset", "Quarter Offset", each ( [Year] - Date.Year(Today) ) * 4 + Date.QuarterOfYear( [Date] ) - Date.QuarterOfYear( Today ), Int64.Type),
  #"Add Year Offset" = Table.AddColumn(#"Add Quarter Offset", "Year Offset", each [Year] - Date.Year(Today), Int64.Type),
  #"Insert Is Weekend" = Table.TransformColumnTypes(Table.AddColumn(#"Add Year Offset", "Is Weekend", each if [Day of Week Number]= 1 or [Day of Week Number]=7 then "True" else "False"), {{"Is Weekend", type logical}}),
  #"Insert Is Weekday" = Table.TransformColumnTypes(Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if [Day of Week Number] <= 6 and [Day of Week Number]>1 then "True"  else "False"), {{"Is Weekday", type logical}}),
  #"Duplicated column" = Table.DuplicateColumn(#"Insert Is Weekday", "Date Integer", "Date Integer - Copy"),
  #"Inserted first characters" = Table.AddColumn(#"Duplicated column", "First characters", each Text.Start(Text.From([#"Date Integer - Copy"]), 6), type text),
  #"Changed column type" = Table.TransformColumnTypes(#"Inserted first characters", {{"First characters", Int64.Type}}),
  #"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Date Integer - Copy"}),
  #"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"First characters", "Calander Year Month Int"}}),
  #"Added custom" = Table.AddColumn(#"Renamed columns", "Month Year", each Date.ToText([Date], "MMM yy")),
  #"Removed columns 1" = Table.RemoveColumns(#"Added custom", {"Month-Year"}),
  #"Transform columns" = Table.TransformColumnTypes(#"Removed columns 1", {{"Month Year", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Month Year", null}}),
  #"Added custom 1" = Table.AddColumn(#"Replace errors", "Year Quarter Int", each Text.Start([#"YYYY-MM"],4) & Number.ToText([Quarter Number])),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Added custom 1", {{"Year Quarter Int", Int64.Type}}),
  #"Added custom 2" = Table.AddColumn(#"Changed column type 1", "Quarter Year ", each [Quarter] & " " & Text.Start([#"YYYY-MM"],4)),
  #"Added custom 3" = Table.AddColumn(#"Added custom 2", "Year Quarter", each Text.Start([#"YYYY-MM"],4) & " " & [Quarter]),
  #"Transform columns 1" = Table.TransformColumnTypes(#"Added custom 3", {{"Quarter Year ", type text}, {"Year Quarter", type text}}),
  #"Replace errors 1" = Table.ReplaceErrorValues(#"Transform columns 1", {{"Quarter Year ", null}, {"Year Quarter", null}}),
  #"Renamed columns 1" = Table.RenameColumns(#"Replace errors 1", {{"Quarter Year ", "Quarter Year"}}),
  #"Added custom 4" = Table.AddColumn(#"Renamed columns 1", "Month Full Year", each [Month Name Short] & " " & Number.ToText([Year])),
  #"Transform columns 2" = Table.TransformColumnTypes(#"Added custom 4", {{"Month Full Year", type text}}),
  #"Replace errors 2" = Table.ReplaceErrorValues(#"Transform columns 2", {{"Month Full Year", null}}),
  #"Merged queries" = Table.NestedJoin(#"Replace errors 2", {"Date"}, Holidays, {"Date"}, "Holidays", JoinKind.LeftOuter),
  #"Expanded Holidays" = Table.ExpandTableColumn(#"Merged queries", "Holidays", {"Holiday"}, {"Holiday"}),
  #"Added custom 5" = Table.AddColumn(#"Expanded Holidays", "Is Holiday", each if [Holiday] = null then "False" else "True"),
  #"Added custom 6" = Table.AddColumn(#"Added custom 5", "First Business Day of Year", each Date.AddDays(Date.StartOfYear([Date]) ,1)),
  #"Added custom 7" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom 6", "Business Day of Year", each BusinessDays([First Business Day of Year], [Date], HolidayList)), {{"Business Day of Year", Int64.Type}}),
  #"Added custom 8" = Table.AddColumn(#"Added custom 7", "YearBusinessDayInt", each if [Business Day of Year] < 10 then  Number.ToText([Year]) & "00" & Number.ToText([Business Day of Year])
else if [Business Day of Year] < 100 then 
Number.ToText([Year]) & "0" & Number.ToText([Business Day of Year]) else Number.ToText([Year]) & Number.ToText([Business Day of Year])),
  #"Changed column type 2" = Table.TransformColumnTypes(#"Added custom 8", {{"YearBusinessDayInt", Int64.Type}}),
  #"Added custom 9" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type 2", "Business Day Adjusted", each if ([Day Name Short] = "Sat" and [Day of Month Number]=1) then [Business Day of Year]+1 else
if ([Day Name Short] = "Sun" and [Day of Month Number]=2) then [Business Day of Year]+1 else 
if ([Day Name Short]="Sun" and [Day of Month Number]=1) then [Business Day of Year]+1 else
if ([Is Holiday] = "True" and [Day of Month Number]=2 and [Day Name Short]="Mon") then [Business Day of Year]+1 else 
if ([Month Name Short]="Jan" and  [Day of Month Number]=1) then 1
else [Business Day of Year]), {{"Business Day Adjusted", Int64.Type}}),
  #"Transform columns 3" = Table.TransformColumnTypes(#"Added custom 9", {{"Is Holiday", type text}, {"First Business Day of Year", type text}}),
  #"Replace errors 3" = Table.ReplaceErrorValues(#"Transform columns 3", {{"Is Holiday", null}, {"First Business Day of Year", null}})
in
  #"Replace errors 3"

本文标签： powerbiDataflow forcing a List to be a TableStack Overflow