Change Source from File to Folder?

[CoreyP]

Hey guys,

I started on my current report with a single xlsx file as the data source while I tried to figure out all the steps and DAX I needed to create a functioning model. Now that I've completed everything I need to, I want to change the source to a folder. The xlsx files in the folder are the same as the initial xlsx document source, just for different date ranges of data. 

How can I change my source type? I know how to change it to a different xlsx document, but there isn't a way to change the type.. 

Here's the code from my initial query: 

let
    Source = Excel.Workbook(File.Contents("C:\Users\coreypfeifer\Documents\_Business Analytics by Corey\_Order Fulfillment\KPI\BARRE3\02.26.18-03.25.18.xlsx"), null, true),
    #"KPI Report_Sheet" = Source{[Item="KPI Report",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KPI Report_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORD NO", Int64.Type}, {"REF ORD NO", type text}, {"CARRIER", type text}, {"SHIP DATE", type date}, {"DUE DATE", type date}, {"ORDER CLOSED TSTAMP", type datetime}, {"PICK COMPLETED TSTAMP", type datetime}, {"ORDER RCV DATE", type datetime}, {"NO OF LINES", Int64.Type}, {"ADDR NAME", type text}, {"SHIPPED QTY", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"ORD NO", "Order Number"}, {"REF ORD NO", "REF Order Number"}, {"CARRIER", "Carrier"}, {"SHIP DATE", "Ship Date"}, {"DUE DATE", "Due Date"}, {"ORDER CLOSED TSTAMP", "Order Closed TStamp"}, {"PICK COMPLETED TSTAMP", "Pick Complete TStamp"}, {"ORDER RCV DATE", "Order Received TStamp"}, {"NO OF LINES", "Count of SKUs"}, {"ADDR NAME", "Ship To Name"}, {"SHIPPED QTY", "Total Units"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Order Received TStamp", "Order Number", "REF Order Number", "Ship Date", "Due Date", "Ship To Name", "Count of SKUs", "Total Units", "Carrier", "Pick Complete TStamp", "Order Closed TStamp"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if Text.StartsWith([REF Order Number], "W") then "Wholesale" else if Text.StartsWith([REF Order Number], "S") then "Wholesale" else "Retail"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Order Received TStamp", "Order Number", "REF Order Number", "Custom", "Ship Date", "Due Date", "Ship To Name", "Count of SKUs", "Total Units", "Carrier", "Pick Complete TStamp", "Order Closed TStamp"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Custom", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Order Type"}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [Order Type] = "Retail" then 1 else if [Order Type] = "Wholesale" then 3 else null),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column1",{"Order Received TStamp", "Order Number", "REF Order Number", "Order Type", "Custom", "Ship Date", "Due Date", "Ship To Name", "Count of SKUs", "Total Units", "Carrier", "Pick Complete TStamp", "Order Closed TStamp"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns2",{{"Custom", type duration}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Order Type Deadline"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns2", {{"Order Received TStamp", type text}}, "en-US"), "Order Received TStamp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Order Received TStamp.1", "Order Received TStamp.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Order Received TStamp.1", type date}, {"Order Received TStamp.2", type time}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Order Received TStamp.1", "Order Received Date"}, {"Order Received TStamp.2", "Order Received Time"}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns3", {{"Pick Complete TStamp", type text}}, "en-US"), "Pick Complete TStamp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Pick Complete TStamp.1", "Pick Complete TStamp.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Pick Complete TStamp.1", type date}, {"Pick Complete TStamp.2", type time}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Changed Type4",{{"Pick Complete TStamp.1", "Pick Complete Date"}, {"Pick Complete TStamp.2", "Pick Complete Time"}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns4", {{"Order Closed TStamp", type text}}, "en-US"), "Order Closed TStamp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Order Closed TStamp.1", "Order Closed TStamp.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Order Closed TStamp.1", type date}, {"Order Closed TStamp.2", type time}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Changed Type5",{{"Order Closed TStamp.1", "Order Closed Date"}, {"Order Closed TStamp.2", "Order Closed Time"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns5",{"Order Type Deadline"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Removed Columns",{{"Order Number", type text}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Changed Type6",{{"Total Units", "Units"}})
in
    #"Renamed Columns6"

And here is the code from a New Query i created directing to the folder. I want to keep all of the applied steps from above, just added to all the files.

let
    Source = Folder.Files("C:\Users\coreypfeifer\Documents\_Business Analytics by Corey\_Order Fulfillment\KPI\BARRE3"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content],true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Kind] = "Sheet"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"ORD NO", "REF ORD NO", "CARRIER", "SHIP DATE", "DUE DATE", "ORDER CLOSED TSTAMP", "PICK COMPLETED TSTAMP", "ORDER RCV DATE", "NO OF LINES", "ADDR NAME", "SHIPPED QTY"}, {"ORD NO", "REF ORD NO", "CARRIER", "SHIP DATE", "DUE DATE", "ORDER CLOSED TSTAMP", "PICK COMPLETED TSTAMP", "ORDER RCV DATE", "NO OF LINES", "ADDR NAME", "SHIPPED QTY"})
in
    #"Expanded Data"

Can you please help me out here? 

[MFelix · Accepted solution]

Hi @CoreyP,

When you make a connection to a folder you have to the folder you have  3 options/sub-options on the bottom :

• Combine
  • Combine & Edit
  • Combine & Load
• Load
• Edit

If you choose the first one with the option of Edit you will get created function that allows you to make a mockup of your data treatment that then will be applied to all file on your folder check the link to get further information.

You can then copy all of your steps from the first query to the table with the name Transform Sample File From FileName and get the expected result.

This can also be made by hand if you want to be a little more insightfull of what is being done chech this link with another example

Regards,

MFelix

[DebbieE]

Hi ,

Im having the same issue, I started with a single csv file. Lots of steps were then created to transform the data, I then added lots of new measures etc and reports

I now want to change the datasource to a folder with multiple files. I clicked on source in Applied Steps but you have to specify a file name to change it. I then went to New source and created my new folder source which just created me a new data set with none of my steps or new measures against it. All my tiles are based on the previous data source.

Im not sure what you mean by...

"You can then copy all of your steps from the first query to the table with the name Transform Sample File From FileName and get the expected result."

To me it looks like you cant change from a single file data source to a folder. Any help would be appreciated.

[MFelix]

Hi @DebbieE,

If you go to the Advance Editor in the query view, you can replace the steps you have to the ones of the single file.

You need to mkae a new query based on a folder, then go to your first query and copy in the advance editor view the steps of the transformation of the data, go to the Transform Sample File query and copy the steps also in the advance view editor, this will change the way the sample file is treated without having to repeat every step.

Regards,

MFelix

[DebbieE]

Thank you, that will save some time