Help with column not found....but column is really not found...anywhere_

[jburbano]

From PBI Desktop is works fine:

let
    Source = Odbc.Query("dsn=ServiceNow", "SELECT * FROM sc_item_option_mtom WHERE sc_item_option_mtom.dv_request_item IN (SELECT dv_parent FROM change_request WHERE (active = 1) AND (sys_created_on >= TIMESTAMPADD(SQL_TSI_MONTH, " & Text.From(AddMonths) & ", NOW())))"),
    #"Added Custom" = Table.AddColumn(Source, "KeyValue", each Table.First(Odbc.Query("dsn=OURITSMSYstems", "SELECT value,dv_item_option_new FROM sc_item_option WHERE (sys_id = '" & [dv_sc_item_option] &"')"))),
    #"Expanded KeyValue" = Table.ExpandRecordColumn(#"Added Custom", "KeyValue", {"value", "dv_item_option_new"}, {"value", "dv_item_option_new"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded KeyValue",{{"dv_item_option_new", type text},{"value", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dv_item_option_new] <> null and [dv_item_option_new] <> "" and [dv_item_option_new] <> " " and [value] <> null and [value] <> "" and [value] <> " "),
    #"Fuzzy Cluster" = Table.AddFuzzyClusterColumn(#"Filtered Rows", "dv_item_option_new", "dv_item_option_new_fuzzy", [Threshold = 0.95]) as table,
    #"Filtered Rows1" = Table.SelectRows(#"Fuzzy Cluster", each Text.Contains([dv_item_option_new], "File Name(s) - Path - Date")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"dv_item_option_new"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"dv_request_item"}, {{"keyValues", each (
        let
            ritmGrouping = Table.RemoveColumns(_,{"request_item", "dv_request_item", "sc_item_option", "dv_sc_item_option", "sys_created_by", "sys_created_on", "sys_id", "sys_mod_count", "sys_tags", "sys_updated_by", "sys_updated_on"}),
            pivotGrouping = Table.Pivot(ritmGrouping, List.Distinct(ritmGrouping[dv_item_option_new_fuzzy]), "dv_item_option_new_fuzzy", "value")
        in
            pivotGrouping
    )    
    , type table [value=text, dv_item_option_new_fuzzy=text]}}),
    #"Expanded keyValues" = Table.ExpandTableColumn(#"Grouped Rows", "keyValues",Table.ColumnNames(Table.Combine(#"Grouped Rows"[keyValues])))
in
    #"Expanded keyValues"

It is getting custom options from a ticket system and dynamically creating columns for each ticket where it is null if it does not have the option available.  In Desktop, it works perfect, but in service, I am getting the following:

The '<oii>File Name(s) - Path - Date Time Stamp / Version Id</oii>' column does not exist in the rowset. Table: sc_item_option_mtom_calculated.

Looking in my data:

This is from the table, before I pivot the data to columns.  The mention column 'File Name(s) - Path - Date Time Stamp / Version Id' does indeed not exists there...but it shouldn't so not sure where the heck it is even seeing it?  

Not sure if the '/' is causing an escape sequence during hte service refresh. As I mentioned, it refreshes fine on PBI desktop.  It fails in service.

[watkinnc]

Have you tried clearing the cache under Options>Data?

--Nate

[jburbano]

It works in desktop though...It is in service that it does not.