Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm using the 'Date Created' colunm when pulling data from a Folder of files in order to keep track of changes when new files are applied.
After changing the Date Created from Date/Time to just Date. I select the Date Created and the Content column and remove the other columns.
Once I combine the files I have the Date Created Column with header "Date Created" and all the rest of the information.
The problem is that the files that the header column names of other columns that I'm pulling in don't always go to the top, leaving the column names as, 'Date Created', Column1, Column2, Column3.......
If I Use the first row as headers the 'Date Created' is replaced by the Date from the first row and all the rest of the columns have the proper names for the data below them. I can change the date back to 'Date Create' but when I run the query in the future it will always be looking for that specific date that was promoted....this will cause a problem if/when I remove older files.
What can be done here? I seem to remember a tutorial where you could remove a column as an applied step only to put it back in later by referencing a previous applied step. I can't find that tutorial.
Hi @RobRayborn,
Instead of promoting the first row, grab it's values: Record.ToList( Source{0} )
Now you can replace that date value by its position in the list, here its the first position =0 and replacing 1 value:
List.ReplaceRange( FirstRowAsList, 0, 1, {"Create Date"} )
Learn more about List.ReplaceRange
Now you can use this list to rename your columns and remove the first row afterwards.
To illustrate, here's an example
let
FirstRowAsList = Record.ToList( Source{0} ),
UpdateListValues = List.ReplaceRange( FirstRowAsList, 0, 1, {"Create Date"} ),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJW0lHyS8xNNYTSRlAaJm6iFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Created" = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
RenameCols = Table.RenameColumns( Source, List.Zip( { Table.ColumnNames(Source), UpdateListValues} ) )
in
RenameCols
with this result.
Ps. If this helps you solve your query, please mark it as solution. Thanks!
This may work, but I don't know where I would add it to my current Get Data>>From Folder.
let
Source = Folder.Files("C:\Users\xxxxxxxxxxx - xxxxxxxxxxxxxxxxx\xxxxxxxxxx\xx"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date created", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date created", "Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Date created", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.