Power query question

[JamesBowdidge]

Hi,

I have followed the below guide on setting up a folder query and it 99% solves my issue.. However the last part I cannot work out is I only want to return the 4th row from each worksheet in each workbook..

anyone suggest a way of acheiving this? I am so close!!

Retrieve Values from Many Workbooks - Excel University

Need an efficient way to pull data from various workbooks, even when those workbooks are updated regularly? Here's how to do it using Get & Transform!

www.excel-university.com

thanks

J

 

[sandy666]

without seeing whole project and M-code I can suggest
Table.Range(table as table, offset as number, optional count as nullable number) as table

 

[JamesBowdidge]

thanks, I think the below is the M-code you refer too (im new to this part of excel) - this is taken from the advanced editor window..

Where should I add the code from your reply? Also do I need to replace the words with numbers in your code? IE this part "(table as table, offset as number, optional count as nullable number)"

thanks for your help!

let
Source = Folder.Files("C:\Users\jbowdidge\OneDrive - Sopra Steria\James Bowdidge\WFM\Manifest"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Estate")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook(File.Contents([Folder Path]&[Name]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Belfast" or [Custom.Name] = "Birmingham" or [Custom.Name] = "Birmingham Premium Lounge" or [Custom.Name] = "Cardiff" or [Custom.Name] = "Cardiff Bay" or [Custom.Name] = "Croydon" or [Custom.Name] = "Glasgow" or [Custom.Name] = "Manchester" or [Custom.Name] = "Manchester (Fountain St)" or [Custom.Name] = "Manchester Premium Lounge" or [Custom.Name] = "Mark Lane Premium Lounge")),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom.Data", {"Column4", "Column33", "Column34", "Column35"}, {"Custom.Data.Column4", "Custom.Data.Column33", "Custom.Data.Column34", "Custom.Data.Column35"})
in
#"Expanded Custom.Data"

 

[sandy666]

Post#2 contain definition of the function, if your code generate end result and you want 4th row only, I think it can be: FourthRow = Table.Range(#"Expanded Custom.Data" ,4,1) used as last line of the code.

and yes, this is M-code but would be nice if you will use CODE tags.

let
    Source = Folder.Files("C:\Users\jbowdidge\OneDrive - Sopra Steria\James Bowdidge\WFM\Manifest"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Estate")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook(File.Contents([Folder Path]&[Name]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "Belfast" or [Custom.Name] = "Birmingham" or [Custom.Name] = "Birmingham Premium Lounge" or [Custom.Name] = "Cardiff" or [Custom.Name] = "Cardiff Bay" or [Custom.Name] = "Croydon" or [Custom.Name] = "Glasgow" or [Custom.Name] = "Manchester" or [Custom.Name] = "Manchester (Fountain St)" or [Custom.Name] = "Manchester Premium Lounge" or [Custom.Name] = "Mark Lane Premium Lounge")),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom.Data", {"Column4", "Column33", "Column34", "Column35"}, {"Custom.Data.Column4", "Custom.Data.Column33", "Custom.Data.Column34", "Custom.Data.Column35"}),
    FourthRow = Table.Range(#"Expanded Custom.Data" ,4,1)
in
    FourthRow