Results 1 to 2 of 2

Power Querry Dynamic Path <ERROR>

Threaded View

Shivex Power Querry Dynamic Path... 10-06-2023, 07:57 PM
Shivex Re: Power Querry Dynamic Path... 10-06-2023, 07:59 PM
  1. #1
    Registered User
    Join Date
    11-13-2021
    Location
    Mexico
    MS-Off Ver
    365
    Posts
    2

    Power Querry Dynamic Path <ERROR>

    I've created a dynamic path in Excel to be able to collect data from a folder for wherever I place it, it works.
    However, I do get an error sporadically that it's unable to find the folder, even though it's working. Any way to remove that?

    I got a reference table named DynamicPath with the code:
    =LEFT(CELL("filename"),SEARCH("\[",CELL("filename")))
    let
        Source = Excel.CurrentWorkbook(){[Name="DynamicPath"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Path", type text}}),
        Path = #"Changed Type"{0}[Path],
      GetFolderFiles = Folder.Files(Path),
        #"Filtered Rows" = Table.SelectRows(GetFolderFiles, each ([Name] = "1. Central Data.xlsx")),
        #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Per Package", type number}, {"Usable Amount", Int64.Type}, {"Unit", type text}, {"Purchase Price", type number}, {"Category", type text}, {"Supplier", type text}, {"Classification", type text}, {"Payment", type text}, {"Alc %", type number}, {"Portfolio", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type1", "Price Per Unit", each [Purchase Price]/[Per Package]/[Usable Amount]),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Source.Name", "Name", "Per Package", "Usable Amount", "Price Per Unit", "Unit", "Purchase Price", "Category", "Supplier", "Classification", "Payment", "Alc %", "Portfolio"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Price Per Unit", Currency.Type}, {"Purchase Price", Currency.Type}, {"Usable Amount", type number}, {"Per Package", type number}, {"Alc %", Percentage.Type}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Supplier", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
        #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Source.Name", "Index", "Name", "Per Package", "Usable Amount", "Price Per Unit", "Unit", "Purchase Price", "Category", "Supplier", "Classification", "Payment", "Alc %", "Portfolio"})
    in
        #"Reordered Columns1"
    Excel Error.png
    Last edited by Shivex; 10-06-2023 at 07:58 PM. Reason: found error

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power query get data from folder (dynamic path)
    By Undo in forum Excel General
    Replies: 1
    Last Post: 06-19-2023, 03:17 PM
  2. Dynamic path for Power Query with multiple sub folders
    By Shmurkam in forum Excel General
    Replies: 0
    Last Post: 02-22-2022, 01:26 PM
  3. Power Query Dynamic Folder Path
    By jasond1992 in forum Excel General
    Replies: 0
    Last Post: 05-18-2021, 03:07 AM
  4. Dynamic path and Filename in Power Query
    By AllisterB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2021, 04:57 PM
  5. Excel 2016 - Power querry hep!!!!
    By sandeep2872003 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2019, 07:30 AM
  6. Excel Power Querry - reuse querries
    By dianaCatz in forum Excel General
    Replies: 1
    Last Post: 01-26-2019, 09:08 AM
  7. Changing connection string path to a dynamic path
    By Shevi in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-11-2014, 05:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1