Power Platform Community Forum Thread Details

Hello,

First off, here is my excel version:

What I've done is created a query that locates the content of a sharepoint folder seen below:

After that, I referenced this query for a series of new queries. This is an example of my "new accounts opened" query which is functioning correctly.

let
Source = SharepointFolder,
#"New Accounts Opened1" = Source{[Name="New Accounts Opened"]}[Content],
#"Sorted Rows" = Table.Sort(#"New Accounts Opened1",{{"Name", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Filtered Hidden Files1" = Table.SelectRows(#"Kept First 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 Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Account Number", type text}, {"Account RR Code", type text}, {"Account Name", type text}, {"Account Short Name", type text}, {"Account Open Date", Int64.Type}, {"Last Trade Date", Int64.Type}, {"Account Credit Code", type text}, {"Account Debit Code", type text}, {"Account Currency", type text}, {"54-101 Coding", Int64.Type}, {"Account Trade Date Balance", type number}, {"Account Settlement Date Balance", type number}, {"Account Market Value", type number}, {"Current Status of account", Int64.Type}, {"Account Type", type text}, {"Account Service Code", Int64.Type}, {"Client Code", type text}, {"Broker Code", type text}})
in
#"Changed Type"

As you can see in the pane, it's a CSV file.

unfortunately, if I try this with "new accounts pending" query it doesn't work.

This is the error code I receive:

We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again.

I would rather not convert xlsx files into CSV just for my tool to work, so any help would be greatly appreciated.

Cheers,

Categories:

Power Query