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!
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"})
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"
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}))
Ah, I got it to work. Apologies for the misunderstanding and thank you very much for your work!
All the best,
Ju
