Microsoft
MicrosoftI have PQ query set up in Excel desktop. The last step is changing the type of the Date column from text to datetime. This works fine in the desktop version. When I refresh in Excel for the Web, the date column ends up blank. I have tried so many workarounds and cannot get anything to work. I need the query to be able to refresh from the web version because many of the users only have access to the web version. Any thoughts?
If I leave it as text, then once the table is inserted into excel, it is converted into a number.
let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "CleanedTable")),
NICU16 = #"Filtered Rows"{[Name="NICU16"]}[Content],
#"Replaced Value" = Table.ReplaceValue(NICU16,null,0,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}),
#"Removed Top Rows" = Table.Skip(#"Replaced Value",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"NICU", "Metric"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Totals"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Metric"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Metric]), "Metric", "Value"),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Pivoted Column", {{"PI-productivity index", 0}}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Errors",{{"Date", type datetime}})
in
#"Changed Type"
MicrosoftHey MattRoor ,
If possible, please share/message/email me with a file with a similar structure so we can test.
Thanks SergeiBaklan for helping out.
I believe here
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"Date", type date}}, "en-US")"en-US" shall be changed on something else, have no idea in which locale MattRoor is.
MattRoor , most probably you have an error transforming Date type, loading to the grid errors are shown as blank cells. I guess you have different regional settings on desktop and on web. Assume on Desktop that's French and dates looks like "10/07/2025". On web usually default regional setting is English (US). Power Query in that region doesn't recognize text "10/07/2025" as date and returns an error.
You may change regional setting on web
at the same more safe transform data type using locale, in your case last step will be
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Errors", {{"Date", type datetime}}, "fr-FR"),
In Power Query editor you may initiate this step from UI using right click menu
As another comment I'd don't hardcode column names, more safe if first steps are like
let
NICU16 = Excel.CurrentWorkbook(){[Name="NICU16"]}[Content],
#"Replaced Value" = Table.ReplaceValue(NICU16,null,0,Replacer.ReplaceValue, Table.ColumnNames(NICU16) ),
#"Removed Top Rows" = Table.Skip(#"Replaced Value",1),
...SergeiBaklan , thank you so much for your help and guidance. I have checked my regional settings on the web version, and it is/was set to English(US). As well, I have adjusted my last step of the query to Change Type Using Locale and set to English(US). I still end up with blanks being returned to the grid. Any other suggestions? Also, thank you for the guidance on hardcoding column names. I will certainly take that into account on my final query.
Here is my revised query code where you can see the Using Locale.
let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "CleanedTable")),
NICU16 = #"Filtered Rows"{[Name="NICU16"]}[Content],
#"Removed Top Rows" = Table.Skip(NICU16,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"NICU", "Metric"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Totals"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Metric"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}}),
// Pivot the Metric column
#"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Metric]), "Metric", "Value"),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Pivoted Column", {{"PI-productivity index", 0}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Errors", {{"Date", each Text.BeforeDelimiter(_, " "), type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"Date", type date}}, "en-US")
in
#"Changed Type with Locale"
I have adjusted my last step of the query to Change Type Using Locale and set to English(US)
You shall select here not English (US) but the source region. If dates (more exactly texts which represent dates) are in French default format select French (France). If in UK format, select English (UK), etc. With that they will be correctly converted to dates and such dates will be shown in default client regional format, i.e. English (US) on Web, French on desktop with French regional settings, Chinees on desktop in China, etc.