Forum Discussion

menezesan's avatar
menezesan
Frequent Visitor
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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

  • 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

  • menezesan's avatar
    menezesan
    Frequent 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

    • BA_Pete's avatar
      BA_Pete
      Super 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's avatar
        menezesan
        Frequent 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!

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    Change ExtraValues.Error to ExtraValues.Ignore.

     

    --Nate

    • menezesan's avatar
      menezesan
      Frequent 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: