excel - Only first row displaying in Power Query - Stack Overflow

admin管理员组

文章数量:1129419

I'm compiling reports from a shared folder based off the last refresh date. The problem I'm having is only the first row is returning per sheet.

I've read related posts and one solution that worked was going into the source sheets, saving them, closing and refreshing the query. Ideally I don't want to have to do this step every time. Any idea what the issue could be?

let
Source = Excel.Workbook(File.Contents("My Path"), null, true),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]) in #"Promoted Headers"

I'm expecting the following, however only the first row is coming back

E-mail Subject	Contractor	Transaction Start	Transaction End
Email 123	Contractor Name 123
Email 321	Contractor Name 321

I'm compiling reports from a shared folder based off the last refresh date. The problem I'm having is only the first row is returning per sheet.

I've read related posts and one solution that worked was going into the source sheets, saving them, closing and refreshing the query. Ideally I don't want to have to do this step every time. Any idea what the issue could be?

let
Source = Excel.Workbook(File.Contents("My Path"), null, true),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]) in #"Promoted Headers"

I'm expecting the following, however only the first row is coming back

E-mail Subject	Contractor	Transaction Start	Transaction End
Email 123	Contractor Name 123
Email 321	Contractor Name 321

• excel
• powerquery

Share Improve this question Follow edited Jan 9 at 8:10 IamZuup asked Jan 8 at 9:30 IamZuupIamZuup 2111 silver badge44 bronze badges 4

• It would probably be easier to assist you if you could edit your question to include sample data that will reproduce your problem. Please enter it as text which can be copy/pasted, not a screenshot. You can format it perhaps using this Markdown Tables Generator within a code block. – Ron Rosenfeld Commented Jan 8 at 11:59
• How many rows before and after the #"Invoke Custom Function1" step? If it changes, edit question to post content of that function – horseyride Commented Jan 8 at 15:27
• I've updated to show a more trimmed down version of the code with the issue still remaining. Right at source step, if I go into table, it's showing just the first row. – IamZuup Commented Jan 9 at 7:55
• If the source step fails there is something wrong with the file. Otherwise you are expanding it incorrectly. We don't have the file to help – horseyride Commented Jan 9 at 14:17

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

It looks that the last step filtered the data. You can delete the last step and have a try.

Path = Excel.CurrentWorkbook(){[Name="Month"]}[Content],
Source = Folder.Files(Text.Combine({"Z:\OutputReport\2025\",Path[Month]{0}})),
#"Renamed Columns1" = Table.RenameColumns(Source, {"Name", "Source.Name"}),
#"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns1", "Transform File", each #"Transform File"([Content])),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{Long List of columns}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Source.Name", each Text.BetweenDelimiters(_, "_", "_"), type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Between Delimiters",{{"Source.Name", type date}}),
#"Renamed Date" = Table.RenameColumns(#"Changed Type1",{{"Source.Name", "Date"}}),
#"Merged - Logged Date" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Date", {{"Date", type text}, {"Logged Date", type text}}, "en-GB"),{"Date", "Logged Date"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Logged Date"),
#"Change Type - Logged DateTime" = Table.TransformColumnTypes(#"Merged - Logged Date",{{"Logged Date", type datetime}}),

#"Removed Other Columns" = Table.SelectColumns("Long List of columns"),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Logged Date", type date}})
#"Changed Type2"

本文标签： excelOnly first row displaying in Power QueryStack Overflow