Expand data table only for 1st sheet without specifying sheet name

[jeongkim]

Hi,

I have weekly dataset stored excel files every week and it comes with same format always. 

But the thing is it has various sheet names from different uploaders so I cannot filter with specific name filter.

How can I expand this data table by sheet order?

e.g. expand data for 1st sheet of all stored files no matter what the sheet names are. 

 

let
Source = SharePoint.Contents("https://xxx.sharepoint.com/sites/xxx", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR Cell Status" = #"Shared Documents"{[Name="KR Cell Status"]}[Content],
#"Filtered Rows CT" = Table.SelectRows(#"KR Cell Status", each [Name] = "KT" or [Name] = "SKT" or [Name] = "LGU+"),

#"Listed Columns CT" = Table.ColumnNames(#"Filtered Rows CT"[Content]{0}),
#"Expanded Custom CT" = Table.ExpandTableColumn(#"Filtered Rows CT", "Content", #"Listed Columns CT", List.Transform(#"Listed Columns CT", each "Custom/" & _)),
#"Filtered Rows PM Data - KPI Trend" = Table.SelectRows(#"Expanded Custom CT", each ([#"Custom/Name"] = "PM Data - KPI Trend")),
#"Listed Columns PM Data - KPI Trend" = Table.ColumnNames(#"Filtered Rows PM Data - KPI Trend"[#"Custom/Content"]{0}),
#"Expanded Custom PM Data - KPI Trend" = Table.ExpandTableColumn(#"Filtered Rows PM Data - KPI Trend", "Custom/Content", #"Listed Columns PM Data - KPI Trend", List.Transform(#"Listed Columns PM Data - KPI Trend", each "Custom." & _)),
#"Filtered Rows <> null" = Table.SelectRows(#"Expanded Custom PM Data - KPI Trend", each ([Custom.Name] <> null)),
#"Listed Columns File" = Table.ColumnNames(#"Filtered Rows <> null"[Custom.Content]{0}),
#"Expanded Custom File" = Table.ExpandTableColumn(#"Filtered Rows <> null", "Custom.Content", #"Listed Columns File", List.Transform(#"Listed Columns File", each "Custom-" & _)),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom File",{"Custom-Content", "Custom-Name", "Custom-Date created", "Custom.Name", "Name"}),
#"Added Year" = Table.AddColumn(#"Removed Other Columns", "Year", each Text.Start([#"Custom-Name"], 4)),
#"Added Week" = Table.AddColumn(#"Added Year", "Week", each Text.Middle([#"Custom-Name"], 6, 2)),
#"Added Period" = Table.AddColumn(#"Added Week", "Period", each [Year] & [Week]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Period",{{"Custom-Date created", type date}, {"Week", Int64.Type}, {"Period", Int64.Type}, {"Year", Int64.Type}}),
#"Sorted Rows Descending Period" = Table.Sort(#"Changed Type",{{"Period", Order.Descending}}),

#"Distinct Period" = Table.Distinct(Table.SelectColumns(#"Sorted Rows Descending Period", {"Period"})),
#"Top N= 104 Period" = Table.FirstN(#"Distinct Period", 104),
#"Filtered Rows Period >= 25R1 Profile Data" = Table.SelectRows(#"Top N= 104 Period", each [Period] >= 202511),
#"Joined Period and File" = Table.NestedJoin(#"Sorted Rows Descending Period", {"Period"}, #"Filtered Rows Period >= 25R1 Profile Data", {"Period"}, "Period Mapping", JoinKind.Inner),

#"Added Weekly" = Table.AddColumn(#"Joined Period and File", "Weekly", each if [Year] = Date.Year(DateTime.LocalNow()) and [Week] >= Date.WeekOfYear(DateTime.LocalNow()) - 4 then "Keep" else "Remove"),
#"Added Monthly" = Table.AddColumn(#"Added Weekly", "Monthly", each if [Week] = 5 or [Week] = 9 or [Week] = 13 or [Week] = 18 or [Week] = 22 or [Week] = 26 or [Week] = 31 or [Week] = 35 or [Week] = 39 or [Week] = 44 or [Week] = 48 or [Week] = 52 then "Keep" else "Remove"),
#"Merged Weekly Monthly" = Table.AddColumn(#"Added Monthly", "Weekly/Monthly", each [Weekly] & " " & [Monthly]),
#"Filtered Rows <> Remove Remove" = Table.SelectRows(#"Merged Weekly Monthly", each ([#"Weekly/Monthly"] <> "Remove Remove")),
#"Add Custom Table for Value"= Table.AddColumn(#"Filtered Rows <> Remove Remove", "Custom", each Excel.Workbook([#"Custom-Content"])),
#"Listed Columns Value" = Table.ColumnNames(#"Add Custom Table for Value"[Custom]{0}),
#"Expanded Custom Value" = Table.ExpandTableColumn(#"Add Custom Table for Value", "Custom", #"Listed Columns Value", List.Transform(#"Listed Columns Value", each "Custom/" & _)),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom Value", each ([#"Custom/Hidden"] = false)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom.Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Custom.Name.1", "Custom.Name.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom-Content", "Period Mapping"}),

#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns Fixed Header" = Table.RenameColumns(#"Promoted Headers",
{{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Date Created"},
{Table.ColumnNames(#"Promoted Headers"){2}, "Frequency"},
{Table.ColumnNames(#"Promoted Headers"){3}, "Region"},
{Table.ColumnNames(#"Promoted Headers"){4}, "CT"},
{Table.ColumnNames(#"Promoted Headers"){5}, "Year"},
{Table.ColumnNames(#"Promoted Headers"){6}, "Week"},
{Table.ColumnNames(#"Promoted Headers"){7}, "Period"},
{Table.ColumnNames(#"Promoted Headers"){8}, "Last 5 Weeks"},
{Table.ColumnNames(#"Promoted Headers"){9}, "Last Week of Month"},
{Table.ColumnNames(#"Promoted Headers"){10}, "Keep or Remove"}})
in
#"Renamed Columns Fixed Header"

[v-sdhruv]

Hi @jeongkim ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @jeongkim ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @jeongkim ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @jeongkim ,
You can directly start with blank query.
You can use this query and modify as per your folder name-
let
Source = Folder.Files("C:\\Your\\Folder\\Path")                          // Change this to your folder path
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),
AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each try Excel.Workbook([Content], null, true){0}[Data] otherwise null),
RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null),
ExpandedData = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet])),
FinalTable = Table.SelectColumns(ExpandedData, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet]))
in
FinalTable
This will get you one table with the first sheet from every file
If column names are inconsistent across sheets, consider normalizing them before combining.
Hope this helps!

[v-sdhruv]

Hi @jeongkim ,
You can follow these steps:
After getting data from your SharePoint by:

Get Data → SharePoint Folder
Go to Home > Get Data > SharePoint Folder
Paste the root SharePoint site URL (not the folder URL)

1. Filter to your target folder
In Power Query, filter down to the files you want. Make sure you're only keeping .xlsx file
Keep these columns -> Content, Name, Folder Path.
2. Add a custom column(FirstSheet) and paste this M code

= let
Source = Excel.Workbook([Content], null, true),
SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"),
FirstSheet = if Table.RowCount(SheetsOnly) > 0 then SheetsOnly{0}[Data] else null
in
FirstSheet

3.Remove [Content] column, and expand the new FirstSheet column:
Click the little expand icon next to FirstSheet column --> Expand all columns in the data.

Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[jeongkim]

Hi any reason it should start from Home - Get data rather than writing blank query? 

Can you rewrite the suggestion with my code? 

I need all the excel file data expansion not locating particular since it is opening data only 1st place sheet but I need to open all of 1st sheets from all the files. 

[danextian]

Write a custom column that connects to the binary files as Excel  workbook

= Excel.Workbook ([Binary files column])

Write another column that connects  specifically  to the first worksheet in the workbook and promotes the first row as the headers.

 = Table.PromoteHeaders((Record.Field(Source{List.PositionOf(Table.Column(Source, "Kind"), "Sheet")}, "Data")), [PromoteAllScalars = true])

[jeongkim]

I dont get it,

can you rewrit with my code? 

[shafiz_p]

Hi @jeongkim To acheive this, follow the steps:

Considering you just loaded a single file in power query and the changes each week with different first sheet name. To dynamically select the first sheet only, regardless of name:

after loading file to power query > go to home > open advance editor and change the following line:

From:

after changes, the editor look like this:

But if have folder connector and combining multiple files and only want to consider the first sheet, then first expand files using any sheet you like. After expansion, click on the Transform Sample File > open advance editor and changes any thing inside curely brackets of souce line with 0. See images:

Replace 1 with 0. Where 1 means second sheet and 0 means first.

Here is the desire transformation:

Hope this helps!!

BR,

Shafiz

[jeongkim]

Thanks but my data is not coming by Transform file from query, it is from location as my code.

So it doesn't navigate pargicular Sheet.