The power-query-excel-formats's discuss from hohlick

This is common part for sheet relations

Power-Query-Excel-Formats/RowsOutline/ExcelWorksheetsRowOutlines.pq

Lines 111 to 165 in 8909b10

 Source = Excel.Workbook(File.Contents(FullPath), false, true), 

 // leave sheets only 

 FilteredSheets = Table.SelectRows(Source, each ([Kind] = "Sheet")), 

 // sheets in PQ initially in appearance order, i.e. sheets index (despite visibility) 

 AddSheetsIndex = Table.AddIndexColumn(FilteredSheets, "Index", 1, 1), 

 // check SheetNames parameter 

 SheetNames = if SheetNames is text then {SheetNames} else if SheetNames is list then SheetNames else null, 

 // filter sheets by name if provided 

 FilteredByNames = if SheetNames = null or List.IsEmpty(SheetNames) then AddSheetsIndex else Table.SelectRows(AddSheetsIndex, each List.Contains(SheetNames, [Name])), 

 // UnZip file 

 UnZipped = Table.Buffer(fnUnZip(File.Contents(FullPath))), 

 /* 

 let 

 Source = Folder.Files(Folder), 

 file = Source{[Name = FileName, Folder Path = Folder & "\"]}[Content], 

 UnZippedFile = Table.Buffer(fnUnZip(file)) 

 in 

 Table.Buffer(UnZippedFile), 

 */ 

 // relations id table for sheets 

 workbook = 

 let 

 Source = UnZipped, 

 Content = Source{[FileName ="xl/workbook.xml"]}[Content], 

 ImportedXML = Xml.Tables(Content,null,TextEncoding.Utf8), 

 sheetsTable = ImportedXML{[Name = "sheets"]}[Table], 

 sheetTable = sheetsTable{[Name = "sheet"]}[Table], 

 ExpandedRel = Table.ExpandTableColumn(sheetTable, "http://schemas.openxmlformats.org/officeDocument/2006/relationships", {"Attribute:id"}, {"Attribute:id"}), 

 typed = Table.TransformColumnTypes(ExpandedRel,{{"Attribute:name", type text}, {"Attribute:sheetId", Int64.Type}, {"Attribute:id", type text}}) 

 in 

 typed, 

 // sheets relations id to XML target files 

 workbook_rels = 

 let 

 Source = UnZipped, 

 Filtered = Table.SelectRows(Source, each [FileName]="xl/_rels/workbook.xml.rels"), 

 GetXML = Table.TransformColumns(Filtered, {"Content", each Xml.Tables(_,null,65001)}), 

 XMLContent = GetXML{0}[Content]{[Name="Relationship"]}[Table], 

 FilteredSheetsRel = Table.SelectRows(XMLContent, each [#"Attribute:Type"] = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"), 

 Removed = Table.RemoveColumns(FilteredSheetsRel,{"Attribute:Type"}) 

 in 

 Removed, 

 // merge relations id (via sheets name) 

 MergedRelationsID = Table.Join(FilteredByNames, {"Name"}, workbook, {"Attribute:name"}), 

 // join workbook relations 

 MergedRelationsTarget = Table.Join(MergedRelationsID,{"Attribute:id"},workbook_rels,{"Attribute:Id"}),

Add parameters

Parameters

exclude WSRel
add column number (R1C1) as parameter
add column letter (A1) as parameter

With column parameter - make pre-selection of cells (dont sure if it will help performance).

Replace argument FullPath with FileContent

Hi Max,
I tried to use this function in one of my solutions. However, current version cannot be used for Scheduled Refresh as Power BI can't determine data source.

I changed function - replaced argument FullPath with FileContent.
So, in query it look like
FullPath = "C:\Temp\File.xlsx"
file = File.Contents(FullPath)
Source = fGetNumberFormats( file , "SheetName", 1, true)
... and so one...

Scheduled refresh works fine with such structure.

BR,
Ivan

Need to convert to function

this

Optimize code (exclude redundant referrals)
Create inner function to invoke with sheet name (relations) and column('s)

hohlick / power-query-excel-formats Goto Github PK

power-query-excel-formats's Issues

This is common part for sheet relations

Add parameters

Replace argument FullPath with FileContent

Need to convert to function

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	Source = Excel.Workbook(File.Contents(FullPath), false, true),

	// leave sheets only
	FilteredSheets = Table.SelectRows(Source, each ([Kind] = "Sheet")),

	// sheets in PQ initially in appearance order, i.e. sheets index (despite visibility)
	AddSheetsIndex = Table.AddIndexColumn(FilteredSheets, "Index", 1, 1),

	// check SheetNames parameter
	SheetNames = if SheetNames is text then {SheetNames} else if SheetNames is list then SheetNames else null,

	// filter sheets by name if provided
	FilteredByNames = if SheetNames = null or List.IsEmpty(SheetNames) then AddSheetsIndex else Table.SelectRows(AddSheetsIndex, each List.Contains(SheetNames, [Name])),

	// UnZip file
	UnZipped = Table.Buffer(fnUnZip(File.Contents(FullPath))),
	/*
	let
	Source = Folder.Files(Folder),
	file = Source{[Name = FileName, Folder Path = Folder & "\"]}[Content],
	UnZippedFile = Table.Buffer(fnUnZip(file))
	in
	Table.Buffer(UnZippedFile),
	*/

	// relations id table for sheets
	workbook =
	let
	Source = UnZipped,
	Content = Source{[FileName ="xl/workbook.xml"]}[Content],
	ImportedXML = Xml.Tables(Content,null,TextEncoding.Utf8),
	sheetsTable = ImportedXML{[Name = "sheets"]}[Table],
	sheetTable = sheetsTable{[Name = "sheet"]}[Table],
	ExpandedRel = Table.ExpandTableColumn(sheetTable, "http://schemas.openxmlformats.org/officeDocument/2006/relationships", {"Attribute:id"}, {"Attribute:id"}),
	typed = Table.TransformColumnTypes(ExpandedRel,{{"Attribute:name", type text}, {"Attribute:sheetId", Int64.Type}, {"Attribute:id", type text}})
	in
	typed,

	// sheets relations id to XML target files
	workbook_rels =
	let
	Source = UnZipped,
	Filtered = Table.SelectRows(Source, each [FileName]="xl/_rels/workbook.xml.rels"),
	GetXML = Table.TransformColumns(Filtered, {"Content", each Xml.Tables(_,null,65001)}),
	XMLContent = GetXML{0}[Content]{[Name="Relationship"]}[Table],
	FilteredSheetsRel = Table.SelectRows(XMLContent, each [#"Attribute:Type"] = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"),
	Removed = Table.RemoveColumns(FilteredSheetsRel,{"Attribute:Type"})
	in
	Removed,

	// merge relations id (via sheets name)
	MergedRelationsID = Table.Join(FilteredByNames, {"Name"}, workbook, {"Attribute:name"}),

	// join workbook relations
	MergedRelationsTarget = Table.Join(MergedRelationsID,{"Attribute:id"},workbook_rels,{"Attribute:Id"}),