Data Source name used for a date in a line chart

[Anonymous]

Morning and merry xmas to you all 🙂

I have a series of folder named by the date they are created in

Inside each folder i have a text file that act a smy data source.

I want to use the folder name to act as the date to create a line chart is there any chance i can do that?

Thanks a lot

[AnkitBI]

Would have been better If you have provided folder structure and sample data. Based on your description, I have come up with below. Created a Main Folder and two Subfolder with name as DATE, containing text files.

To get folder name, trick it to extract it during exisitng functionality of extracting data from Folders using Combine and Edit. Once your data is extracted , go to Removed Other Columns1 step, Open Source and select Folder Path. This way your subfolder names will be added to final query. Then you can perform simple operations like Split to get Date.

[Anonymous]

I should have included dataset sorry about that.

I did some modifications here, changed to Excel and the tab name is the data.

https://www.dropbox.com/s/2mjtv2371u6nz5c/Liste_rspa-moscow_2018-09-01.xlsx?dl=0

https://www.dropbox.com/s/shsy0vungritsjx/Liste_rspa-moscow_2018-10-05.xlsx?dl=0

[AnkitBI]

Apologies, I didn't get the shared dataset. Files provided are excels only, Is Date your Folder Name and the File Name are the files present in Folder. If yes, then the previous solution provided shall work. Please give it a try.

File Name	Model Size MB	Support Size MB	Date
VAC_A_00.rvt	506.78	181.22	9/1/2018
VAC_A_Auditorium.rvt	1411.3	137.93	9/1/2018
VAC_A_BR_Arch.rvt	198.52	17.22	9/1/2018

[Anonymous]

My bad as i keep working on it.

Here's a more factual dataset. I ended up putting all the data in the same Excel file with the Tab Name being the date.

https://www.dropbox.com/s/uy3cjompnqe3mpq/Moscow%20Revit%20Server%20Data.xlsx?dl=0

The final required result would be to be able to "follow" the File Size trend (for each file) through the "Date" which are the Tab name in a Line Chart.

Thanks a lot for your help on this day 🙂

[AnkitBI · Accepted solution]

PFB solution. Hope this helps. This will extract Data from all the excel sheets dynamically and will add date (sheet Name) as new column. You can copy the entire code or Just copy first two lines and perform any modifications.

let
    FullFilePath = "C:\New folder (2)\Moscow Revit Server Data.xlsx",
    Source = Excel.Workbook(File.Contents(FullFilePath)),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"2018-09-14", type date}, {"File Name", type text}, {"Model Size MB ", type any}, {"Support Size MB ", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"2018-09-14", "Date"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [#"Model Size MB "] <> "Model Size MB ")
in
    #"Filtered Rows"

[AnkitBI]

@Anonymous Please mark it as solution if response resolved your issue.