Log In
Register
Home
Active Member
September 6, 2019
Offline
Hi Everyone,
I have a power query with the data source pulling from a website. An error happens each time I try to refresh because the column name changes accordingly by (t+1) each following day, so the refresh looks for the (t-1) column name instead of the (t+1) name which becomes an error.
To fix it, I go back to the power query editor and delete the #'Changed Type" step until the original source and refresh to pull in the new set of column names, before re-applying the #'Changed Type' step again.
Is there a way to navigate this by, for example, creating variables for each column to refresh rather than by the column name itself?
Here is an example of what the code looks like:letSource = Web.Page(Web.Contents("website")),Data0 = Source{0}[Data],#"Changed Type" = Table.TransformColumnTypes(Data0,{{"A", type text}, {"1", type number}, {"2", type number}, {"3", type number}, {"4", type number}, {"5", type number}})in#"Changed Type"
Thank you in advance for any advice you can provide me and please let me know if my question is unclear at all. All the best!
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ju,
Try this instead, if all columns are type number:
= Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type number}))
Column [A] seems to be text, you can exclude it from the number columns list, you can format it separately:
List.RemoveItems(Table.ColumnNames(Source),{"A"})
Active Member
September 6, 2019
Offline
Hi Catalin,
Thank you for your help on my query. Is there a way to keep Column [A] since I need it for reference? I have implemented this to the code for now but it excludes Column [A].
let
Source = Web.Page(Web.Contents("website")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0, List.Transform(Table.ColumnNames(Data0), each {_, type number}))
in
#"Changed Type"
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ju,
Why are you saying that [A] is excluded? Not seeing anything doing that in your query, it will just convert any column to type number, including [A]. If [A] has alpha chars, those rows will fail to convert, so you have to format [A] separately.
ChangeA= Table.TransformColumnTypes(Data0, {{"A", type text}})
Then, exclude [A] in the step that converts all columns to number:
#"Changed Type" = Table.TransformColumnTypes(Data0, List.Transform(List.RemoveItems(Table.ColumnNames(ChangeA),{"A"}), each {_, type number}))
Answers Post
1 Guest(s)
