Forum Discussion

MPowerQ's avatar
MPowerQ
New Member
2 years ago

Updated data does not transform/load properly from folder

I have monthly data in a folder (Oct - May) that power query is pulling from.  However, when I added June's data to the folder, it did not format/display the data as it should have.  Example of the good data (Oct) and the data that did not load (June) is provided as well as the applied steps in the Power Query.  The data does not populate during applied step 6 if anyone can help troubleshoot.  Thanks!

 

1 - Oct data that displays properly

  

2 - Jun data that does not display

3 - Applied steps to the power query

4 - Applied Step 5 (remove columns)

5 - Applied step 6 (expand)

 

 

Need Help

8 Replies

    • MPowerQ's avatar
      MPowerQ
      New Member
      2 years ago

      Hello adudani ,

       

      Appreciate the reply and link to the video.

       

      How the video setup combing multiple excel files was different than how the contractor setup our power query.  I removed the applied steps and am attempting to combine the data how the video shows, but immediately received the below error.  Was just trying to change the "Binary" fields to "Tables" on the content column as the video shows.  

       

      Formula:

      = Table.TransformColumns(
      Source, {"Content", each Excel.Workbook(_)}

       

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MPowerQ 

     

    Please check if the table of JUNE in Applied Step 5 (remove columns) has content, you can click at space next to "Table" in the cell. 

     

    If it doesn't have data for the file, please go to "Transform Sample File" query. In Home ribbon, open Advanced Editor, copy all its M code and paste it here (remove sensitive information). We'd like to check the M code to find if there is any step may do some filtering operation that might have some effect on the result. 

     

    Best Regards,
    Jing

    • MPowerQ's avatar
      MPowerQ
      New Member
      2 years ago

      Aloha v-jingzhan-msft,

       

      I opened the table.  It does have data in there.  However, I think that the transform sample file applied steps did not apply to the JUNE excel file.  I opened the advanced editor for the transform sample file and the M code is to promote the headers for the source file.  

       

      Transform sample file M code:

       

      let
      Source = Csv.Document(Parameter1,[Delimiter=",", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
      in
      #"Promoted Headers"

       

      How would I be able to apply the transform sample file M code so it also applies to any new files I add in the folder?  

       

      Thanks!

       

      Mark

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi MPowerQ 

         

        The "Transform Sample File" code doesn't include any filtering steps, and you said "I opened the table.  It does have data in there. ", so the transform sample file M code does have been applied to the new file (Step "Invoke Custom Function1" is to apply transform sample file code to all files). The reason may be in the next steps.

         

        Please check the result after expanding the table column (5 - Applied step 6 (expand)). Scroll down or search by JUNE file name to check the data of JUNE file after expanding. If rows of JUNE file display blank or null at this step, this means the problem happens during the expanding process. When expanding tables in a column, it selects columns to expand based on their names in the sample file. So you can check that all the column names in the June file should be the same as those in the sample file. Any invisible leading or tailing space in columns may have an effect. 

         

        Best Regards,
        Jing