Power Query not including new data column on refresh

H

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear All,

Currently, I am getting the data from a folder and it has few fixed text data columns and then months column with numbers in them.

Every month I received the updated file which has the latest month data but my query does not pick the data for the new month and manually I have to adjust the code to include the new month data.

What I have observed is it has hard coded the months name.

Please see the picture, I have highlighted in yellow.

What I need is to automate the process, so when ever new month data column is available, the query should pick it up.

Your help would be really appreciated.
 

Attachments

  • 1.PNG
    1.PNG
    100.3 KB · Views: 24

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
@hananak: If you could copy and paste the actual code besides the picture that shows the indicated section, then it would be much more helpful to the helpers to help to solve the problem.

When you paste your code, please also select the entire code in the editor and click on the M BB Code button on the toolbar. This way the code snippet will be properly highlighted and also provides an easy way to copy it.

1690818086946.png
 
Last edited:
Upvote 0
smozgur said:
@hananak: If you could copy and paste the actual code besides the picture that shows the indicated section, then it would be much more helpful to the helpers to help to solve the problem.

When you paste your code, please also select the entire code in the editor and click on the M BB Code button on the toolbar. This way the code snippet will be properly highlighted and also provides an easy way to copy it.
Click to expand...

Hi, Thanks for your reply. I turned on the BB Toggle code button and pasted the code below. I hope this is what you meant?

Power Query: 
let
    Source = Folder.Files("C:\Users\user\Desktop\Tool"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "TA")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TA.xlsx", type text}, {"L3 Name", type text}, {"L5 Name", type text}, {"L5 Acc", type text}, {"L6 Acc", type text}, {"L7 Acc", type text}, {"CC", type text}, {"CC Desc.", type text}, {"Acc", type text}, {"Acc Desc", type text}, {"Apr-23", type number}, {"May-23", type number}, {"Jun-23", type number}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Merged", each Text.Combine({[L6 Acc], "-", [CC], "-", [Acc]}), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "GL"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Apr-23", "May-23", "Jun-23", "GL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"GL", "Apr-23", "May-23", "Jun-23"}),
    OldColNames = List.Skip (Table.ColumnNames(#"Reordered Columns"),1),
    NewColNames = List.Transform ({1.. List.Count (OldColNames) }, ( n) => "M"& Text.From (n ) & "A"),
    FromTo = List.Zip ({OldColNames,NewColNames}),
    NewNamesCol = Table.RenameColumns(#"Reordered Columns", FromTo),
    #"Appended Query" = Table.Combine({NewNamesCol, Table4})
in
    #"Appended Query"
 
Last edited by a moderator:
Upvote 0
Thanks. I edited your post to apply the BB code to make it looks like the above.

So, what you are doing is, say for Jul 2023, applying the following manual changes to add the July columns. Could you please confirm?

First edit:
Rich (BB code): 
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{
    {"TA.xlsx", type text}, {"L3 Name", type text}, {"L5 Name", type text}, {"L5 Acc", type text}, {"L6 Acc", type text}, {"L7 Acc", type text}, {"CC", type text}, 
    {"CC Desc.", type text}, {"Acc", type text}, {"Acc Desc", type text}, 
    {"Apr-23", type number}, {"May-23", type number}, {"Jun-23", type number}, {"Jul-23", type number}}),

Second edit:
Rich (BB code): 
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Apr-23", "May-23", "Jun-23", "Jul-23", "GL"}),

Third edit:
Rich (BB code): 
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"GL", "Apr-23", "May-23", "Jun-23", "Jul-23"}),
 
Upvote 0
smozgur said:
Thanks. I edited your post to apply the BB code to make it looks like the above.

So, what you are doing is, say for Jul 2023, applying the following manual changes to add the July columns. Could you please confirm?

First edit:
Rich (BB code): 
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{
    {"TA.xlsx", type text}, {"L3 Name", type text}, {"L5 Name", type text}, {"L5 Acc", type text}, {"L6 Acc", type text}, {"L7 Acc", type text}, {"CC", type text}, 
    {"CC Desc.", type text}, {"Acc", type text}, {"Acc Desc", type text}, 
    {"Apr-23", type number}, {"May-23", type number}, {"Jun-23", type number}, {"Jul-23", type number}}),

Second edit:
Rich (BB code): 
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Apr-23", "May-23", "Jun-23", "Jul-23", "GL"}),

Third edit:
Rich (BB code): 
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"GL", "Apr-23", "May-23", "Jun-23", "Jul-23"}),
Click to expand...

Thank you for applying the BB code. I was not sure exactly how to do it.

Yes, currently, I follow the above approach to add new months. I really need to automate this process.
 
Upvote 0
Hard to deal with the code without data and not being able to testing, but here is what should be done. You said fixed text data and it looks like the first 10 columns, then month columns, which will be added more months at each update. So, we can dynamically set the necessary parts as shown below. I will use your original code, remove the part from the middle, and put the part I modified.

Power Query: 
let
    Source = Folder.Files("C:\Users\user\Desktop\Tool"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "TA")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

    /* Modification start here */
    MonthColumns = List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"), 10),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TA.xlsx", type text}, {"L3 Name", type text}, {"L5 Name", type text}, {"L5 Acc", type text}, {"L6 Acc", type text}, {"L7 Acc", type text}, {"CC", type text}, {"CC Desc.", type text}, {"Acc", type text}, {"Acc Desc", type text}} & List.Transform(MonthColumns, each {_, type number})),

    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Merged", each Text.Combine({[L6 Acc], "-", [CC], "-", [Acc]}), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "GL"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns", MonthColumns & {"GL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"GL"} & MonthColumns),
    /* Modification ends here */

    OldColNames = List.Skip (Table.ColumnNames(#"Reordered Columns"),1),
    NewColNames = List.Transform ({1.. List.Count (OldColNames) }, ( n) => "M"& Text.From (n ) & "A"),
    FromTo = List.Zip ({OldColNames,NewColNames}),
    NewNamesCol = Table.RenameColumns(#"Reordered Columns", FromTo),
    #"Appended Query" = Table.Combine({NewNamesCol, Table4})
in
    #"Appended Query"


Ideally, I would also change the #"Changed Type" step to make it dynamic, because even the current code doesn't change all columns as you probably didn't update that part to include the last month. However, again, I don't have a chance to test any of these, so I would first try the first code above, and then give this one a chance to see if it works as you need.

Power Query: 
let
    Source = Folder.Files("C:\Users\user\Desktop\Tool"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "TA")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    ColumnNames = Table.ColumnNames(#"Expanded Table Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",
        List.Transform(List.RemoveLastN(ColumnNames, List.Count(ColumnNames) - 10), each {_, type text}) & 
        List.Transform(List.RemoveFirstN(ColumnNames, 10), each {_, type any})),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

    MonthColumns = List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"), 10),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"TA.xlsx", type text}, {"L3 Name", type text}, {"L5 Name", type text}, {"L5 Acc", type text}, {"L6 Acc", type text}, {"L7 Acc", type text}, {"CC", type text}, {"CC Desc.", type text}, {"Acc", type text}, {"Acc Desc", type text}} & List.Transform(MonthColumns, each {_, type number})),

    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type1", "Merged", each Text.Combine({[L6 Acc], "-", [CC], "-", [Acc]}), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "GL"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns", MonthColumns & {"GL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"GL"} & MonthColumns),

    OldColNames = List.Skip (Table.ColumnNames(#"Reordered Columns"),1),
    NewColNames = List.Transform ({1.. List.Count (OldColNames) }, (n)=> "M"& Text.From (n) & "A"),
    FromTo = List.Zip ({OldColNames,NewColNames}),
    NewNamesCol = Table.RenameColumns(#"Reordered Columns", FromTo),
    #"Appended Query" = Table.Combine({NewNamesCol, Table4})
in
    #"Appended Query"
 
Upvote 0
Solution
Hi Smozgur,

Thank you very much for providing codes. I have tried them and both are working correctly and giving me the desired results.

Would you be able to confirm, which one is more better to use though both are working. Please let me know your preference.

Many Thanks.
 
Upvote 0
hananak said:
Hi Smozgur,

Thank you very much for providing codes. I have tried them and both are working correctly and giving me the desired results.

Would you be able to confirm, which one is more better to use though both are working. Please let me know your preference.

Many Thanks.
Click to expand...
You're welcome.

The second one is better because it also changes types dynamically including the new columns that will be added each month.
 
Upvote 0
smozgur said:
You're welcome.

The second one is better because it also changes types dynamically including the new columns that will be added each month.
Click to expand...

Perfect. Thank you very much again :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
517
Kariba
K
C
  • Solved
Grap balance/value from previous year via Power Query
Replies
2
Views
70
Carl Wiberg
C
P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
372
alex78
A
H
  • Question Question
Need help in Unpivoting Data in Power Query
Replies
1
Views
224
alansidman
alansidman
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
134
Fluff
Fluff

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top