Forum Discussion
Automatic refresh issues - Columns name in CSV file
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
- BA_Pete4 years agoSuper User
Hi menezesan ,
Can you go into Advanced Editor for your query, copy everything in there, and paste it into a code window ( </> button ) here please?
Obscure any sensitive file/server paths using XXX, but keep the code structure in place.
Pete
- menezesan4 years agoFrequent 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!