Forum Discussion
menezesan
4 years agoFrequent Visitor
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 ...
BA_Pete
4 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
menezesan
4 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!