columns added in query editor don't show up in tables

[dixia]

i am adding columns in query editor for my ultimate calendar table, but after apply&close,  those columns don't show up in the table

[v-shex-msft · Accepted solution]

HI @dixia,

I create a sample calendar and add your custom column, they all work well on my side. Any query steps that you invoke previous steps behind these query steps? They may load the old structure and skip new table fields.

let
   Source = List.Dates,
   #"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
   #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Renamed Columns" = Table.RenameColumns(#"Table from List",{{"Column1", "Date"}}),
   #"Added Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
   #"Added MonthNumber" = Table.AddColumn(#"Added Year", "Month Number", each Date.Month([Date])),
   #"Added Month" = Table.AddColumn(#"Added MonthNumber", "Month", each Date.Month([Date])),
   #"Reordered Columns" = Table.ReorderColumns(#"Added Month",{"Date", "Year", "Month Number"}),
   #"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Year", type number}, {"Month Number", type number}, {"Month", type text}}),
   #"==Add General Columns==" = #"Changed Type3",
   #"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [Month Number]),
   #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
   #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
   #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
   #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
   #"Added WeekStart" = Table.AddColumn(#"Added Weekday Name", "WeekStart",each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy")),
   #"Changed Type" = Table.TransformColumnTypes(#"Added WeekStart",{{"WeekStart", type date}}),

   #"Added WeekEnd" = Table.AddColumn(#"Added Weekday Name", "WeekEnd",each Date.ToText(Date.EndOfWeek([Date], Day.Friday),"MM/dd/yyyy")),
   #"Changed Type1" = Table.TransformColumnTypes(#"Added WeekEnd",{{"WeekEnd", type date}})
in
   #"Changed Type1"

Regards,

Xiaoxin Sheng

[Anonymous]

Are you able to post the Power Query code?  The devil will be in the details.  I'm wondering if you have a "Remove Other Columns" step.

[dixia]

i was able to post the code, and see the column when i click on the applied steps.  looked thru all the applied steps and don't see "remove other column" step..  it looks like the view is not refreshed.  tried refresh preview and it didn't work.

[Anonymous]

When i say "Post the code", i'm talking about into this Forum thread so we can see what you are working on.  Its going to be difficult to be helpful if we are blind.

[dixia]

Thanks for replying. 

i started with codes from an online source for ultimate date calendar and wanted to add  two columns for weekstart Sunday and weekend Friday.

/*
    ****This Calendar was created and provided by Avi Singh****
    ****This can be freely shared as long as this text comment is retained.****
    http://www.youtube.com/PowerBIPro
    www.LearnPowerBI.com by Avi Singh
    */

#"==Add General Columns==" = #"Added CurFiscalYearOffset",
#"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
 #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
 #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
 #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
 #"Added WeekStart" = Table.AddColumn(#"Added Weekday Name", "WeekStart",each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy")),
#"Changed Type" = Table.TransformColumnTypes(#"Added WeekStart",{{"WeekStart", type date}}),

#"Added WeekEnd" = Table.AddColumn(#"Added Weekday Name", "WeekEnd",each Date.ToText(Date.EndOfWeek([Date], Day.Friday),"MM/dd/yyyy")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added WeekEnd",{{"WeekEnd", type date}}),

[Anonymous · Accepted solution]

hi @dixia,  The error in your code appears to be on the line #"Added WeekEnd".  Power Query steps refer to other steps to get their data.  Typically this will be the previous step.  If you look at #"Added WeekEnd" you'll see its point to row several rows before.  This means those steps are effectively ignored.  Try change this to

#"Added WeekEnd" = Table.AddColumn(#"Changed Type", "WeekEnd",each Date.ToText(Date.EndOfWeek([Date], Day.Friday),"MM/dd/yyyy")),

[dixia]

Thanks @Anonymous , the added column showed up with your code.  thanks!

[v-shex-msft · Accepted solution]

HI @dixia,

I create a sample calendar and add your custom column, they all work well on my side. Any query steps that you invoke previous steps behind these query steps? They may load the old structure and skip new table fields.

let
   Source = List.Dates,
   #"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
   #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Renamed Columns" = Table.RenameColumns(#"Table from List",{{"Column1", "Date"}}),
   #"Added Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
   #"Added MonthNumber" = Table.AddColumn(#"Added Year", "Month Number", each Date.Month([Date])),
   #"Added Month" = Table.AddColumn(#"Added MonthNumber", "Month", each Date.Month([Date])),
   #"Reordered Columns" = Table.ReorderColumns(#"Added Month",{"Date", "Year", "Month Number"}),
   #"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}, {"Year", type number}, {"Month Number", type number}, {"Month", type text}}),
   #"==Add General Columns==" = #"Changed Type3",
   #"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [Month Number]),
   #"Added MonthYear" = Table.AddColumn(#"Added MonthYearNum", "MonthYear", each [Month] & "-" & Text.End(Text.From([Year]),2)),
   #"Added MonthYearLong" = Table.AddColumn(#"Added MonthYear", "MonthYearLong", each [Month] & "-" & Text.From([Year])),
   #"Added WeekdayNum" = Table.AddColumn(#"Added MonthYearLong", "WeekdayNum", each Date.DayOfWeek([Date]), Int64.Type),
   #"Added Weekday Name" = Table.AddColumn(#"Added WeekdayNum", "Weekday", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
   #"Added WeekStart" = Table.AddColumn(#"Added Weekday Name", "WeekStart",each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy")),
   #"Changed Type" = Table.TransformColumnTypes(#"Added WeekStart",{{"WeekStart", type date}}),

   #"Added WeekEnd" = Table.AddColumn(#"Added Weekday Name", "WeekEnd",each Date.ToText(Date.EndOfWeek([Date], Day.Friday),"MM/dd/yyyy")),
   #"Changed Type1" = Table.TransformColumnTypes(#"Added WeekEnd",{{"WeekEnd", type date}})
in
   #"Changed Type1"

Regards,

Xiaoxin Sheng

[dixia]

thank you @v-shex-msft for the sample calendar.  this works as well