Automatic refresh issues - Columns name in CSV file

[menezesan]

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?

[Anonymous]

Change ExtraValues.Error to ExtraValues.Ignore.

--Nate

[menezesan]

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:

[menezesan]

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_Pete]

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]

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!

[Anonymous]

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

[BA_Pete]

Hi @menezesan ,

In Power Query, select the Source step of your CSV import. Delete the columns argument here:

So it looks like this:

Your query should ow dynamically pick up any/all columns in the CSV source.

Pete