cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Re: Retain file name column when using Folder as a data source - Power Query M

Thank you ImkeF, it works now. I appreciate your propmt response.

 

Regards,

Morvaryt

Highlighted
New Member

Re: Retain file name column when using Folder as a data source - Power Query M

Hi the code for the auto expand for different headers would be awesome!

Highlighted
New Member

Re: Retain file name column when using Folder as a data source - Power Query M

When I try this solution for every entry in the csvs I get a different row in the data for every filepath in the folder regardless of whether the entry appeared in it or not. This makes it impossible to find entries by specifying the csv which is my ultimate goal.

Highlighted
Super User III
Super User III

Re: Retain file name column when using Folder as a data source - Power Query M

Sorry, but I have no clue what you want and what the current problem is you're facing.

 

Any chance to refer more specificly to the examples given and:

1) Point out where your requirement differs from the examples given and

2) Provide some examples of your case?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Regular Visitor

Re: Retain file name column when using Folder as a data source - Power Query M

What would be the code if you were connecting to a folder with fixed width text files rather than CSV files?

Highlighted
Super User III
Super User III

Re: Retain file name column when using Folder as a data source - Power Query M

Sorry, no idea as I haven't done this already. But you can easily find out by using the UI: Start the import from folder process and you will see a table popping up with all files listed with different metadata-field. Choose "Content" and click into one of its fields. A further dialogue might start but at the end you will be able to see the full code in the advanced editor.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Frequent Visitor

Re: Retain file name column when using Folder as a data source - Power Query M

Hello, thanks for your help so far.  I'm curious if this solution works in PowerPivot?  

 

I've tried several variations of your solution and I can't seem to get it to work.

 

I want to retain on each row of the Binary Data CLE/CVO and Actual/Budget.

 

My folder paths:

C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CLE\Actual\
C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Actual\
C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Budget\
C:\Users\User\OneDrive - Corporate\PowerPivot\TB\CVO\Budget\

 

 

My Binary Data:

Capture1.JPG

 

 

This is the code I tried:  

let
Source = Folder.Files("C:\Users\User\OneDrive - Corporate\PowerPivot\TB"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Folder Path],[Delimiter="\", Encoding=1252])))
in
#"Added Custom"

 

Highlighted
Frequent Visitor

Re: Retain file name column when using Folder as a data source - Power Query M

I ended up solving this myself.  I finally decided to use my head and look up table.expandtablecolumn which made it immediately obvious what I was doing wrong.  I'm new to this so I had no reference point for what was actually happening when I used table.expandtablecolumn.  

 

 

let
    Source = Folder.Files("C:\Users\User\OneDrive - Corporate\PowerPivot\TB"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter=",", Encoding=1252]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Folder Path"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Period", "No", "Current Debit/(Credit)", "YTD Debit/(Credit)"}, {"Custom.Period", "Custom.No", "Custom.Current Debit/(Credit)", "Custom.YTD Debit/(Credit)"})
in
    #"Expanded Custom"

 

From here I'll split the Folder Path on "\" and then remove all the Folder Path columns besides the two containing CLE/CVO and Actual/Budget.

Highlighted
Frequent Visitor

Re: Retain Table name in the new column of same tawhen using web as a data source - Power Query

Hi,

Can you advise me on using the Table name in the new column of the table, when we use the data from websource with multiple tables

Highlighted
New Member

Re: Retain file name column when using Folder as a data source - Power Query M

Hi I am trying  a variation of this unsuccessfully. I need to add columns 'Country' and 'Broker' to the tables which reside in the 'Promoted Headers' column. The step needs to be created between steps "Changed Type" and "Promote Headers" Any help greatly appreciate. 

 

let
Source = FolderPath,
Custom1 = Folder.Contents(Source),
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Name", "Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Name", "FolderName"}, {"Content", "FolderContent"}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Renamed Columns", "FolderContent", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Added Custom" = Table.AddColumn(#"Expanded Content", "Custom", each if [Extension] = ".csv" then Csv.Document([Content]) else Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"FolderName", "Name", "Extension", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "PromoteHeaders", each if [Extension] = ".csv" then Table.PromoteHeaders([Custom]) else Table.PromoteHeaders([Custom]{0}[Data])),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "FolderName", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Country", "Broker"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Country", type text}, {"Broker", type text}}),
PromoteHeaders = #"Changed Type"[PromoteHeaders],
#"TableCombine" = Table.Combine(PromoteHeaders),
#"Removed Columns" = Table.RemoveColumns(TableCombine,{"", "Column9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Gross Weight", type number}, {"Net Weight", type number}, {"Value AWB", type number}, {"Origin Total Freight", type number}, {"Non-EU Freight", type number}, {"EU Freight", type number}, {"Value Duty", type number}, {"Total Freight Eur", type number}, {"Import duty", type number}, {"PCS", type number}, {"Customs VAT", type number}}),
#"OutputTable" = Function.Invoke(ParentFunction,{#"Changed Type1",paramCountry,paramBroker})

in
#"OutputTable"

 

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors