Forum Discussion
Automatic refresh issues - Columns name in CSV file
Hello,
I'm having problems with my Automatic Refresh in a reported published.
My source is a CSV file extracted from Jira. When new links are added or deleted in the Jira tickets, the quantity of columns change.
But if a do a manual refresh in Power BI Desktop, the columns quantity/names is updated, but in the automatic refresh after publish It always return the error that the column wasn't found. So I need to republish the report.
I thought in insert a command to erase the source and get the datas againg, but I do not found nothing related.
Someone knows how can I solve it?
7 Replies
- AnonymousNot applicable
Also, if you have a step in which the columns have to be named, like Table.SelectColumns (and a few others), you can add MissingField.Ignore as the last parameter. That way, missing columns get ignored!
--Nate
- menezesanFrequent Visitor
Hi, BA_Pete and Anonymous
I already erased the quantity of columns code and I do not have any code with the name of the columns, but the error keep happening.I cannot know the name of the new colunm or the erased one, since is a dinamic creation in Jira/csv depending on the links users create
If I do a manual refresh it works, but in the schedule time don´t. and that is my main problem- menezesanFrequent Visitor
Sure!
I create a new one as test but is returng the same error. Here is:
Query 1 (only the source)
let Source = Csv.Document(Web.Contents("https://jira-mut.d.bbg/sr/jira.issueviews:searchrequest-csv-current-fields/63365/SearchRequest-63365.csv?delimiter=;", [Headers=[#"Cache-Control"="no-cache, no-store, must-revalidate"]]),[Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"Query 2 (list with columns names)
let Source = Table.ColumnNames(Query1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Inserted Trimmed Text" = Table.AddColumn(#"Converted to Table", "Outward", each if Text.Contains ([Column1],"Outward issue link (Relates") then [Column1] else null, type text), #"Filtered Rows" = Table.SelectRows(#"Inserted Trimmed Text", each ([Outward] <> null)), #"Delete Extra Column" = #"Filtered Rows"[Column1] in #"Delete Extra Column"Query 3 (part of the data returning the error)
let Source = Query1, //Merge columns from all sprints #"Merge Sprint Columns" = Table.CombineColumns(Source,Query2,Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Relates"), //Remove double semicolons (;;) - Sprints #"Remove Double Semicolons" = Table.ReplaceValue(#"Merge Sprint Columns", ";;","",Replacer.ReplaceText, {"Relates"}), //Remove last semicolon, if exist - Sprints #"Remove Last Semicolon" = Table.ReplaceValue(#"Remove Double Semicolons", each [Relates], // for each line in the column MergedSprint each if Text.EndsWith([Relates],";") // Check if the last character of the value is ; then Text.RemoveRange([Relates],Text.Length([Relates])-1) // If the last character of the value is ; remove the last character else [Relates],Replacer.ReplaceText,{"Relates"}), // if the cell value don't contain ; returne the cell value //Get the last Sprint #"Get Last Sprint" = Table.ReplaceValue(#"Remove Last Semicolon", each [Relates], // for each line in the collumn MergedSprint each if Text.Contains([Relates],";") // Check if the value have the character ; then Text.RemoveRange([Relates],0, Text.PositionOf([Relates],";",Occurrence.Last)+1) // if the cell value contain ;, remove all the charecteres from position 0 until last occurrence of ; else [LastSprint], // if the cell value don't contain ; returne the cell value Replacer.ReplaceText,{"Relates"}) in #"Get Last Sprint"The logic is exactly the same when a User Story is in more than 1 sprint and I just need to know the value of the last one, bc of this I have the list in Query 2, to define the columns I want get the value. Bu the quantity changes if the user insert a new split, or link for example!
- AnonymousNot applicable
Change ExtraValues.Error to ExtraValues.Ignore.
--Nate
- menezesanFrequent Visitor
It not solve my problem.
I change this, instead if have all columns
I just have one
and I need to treat all values in all columns, bc I never know in which column I have the value 😞
the data is like this: