

February 1, 2019

Hi Guys,
i have table across multiple workbooks:
Col1 Col2 Col3
1 1 1
2 2 2
plus named ranges in each: R_Surname and R_Month.
Now i would like to append all tables into one master workbook and get:
Col1 Col2 Col3 Surname Month
1 1 1 Smith January
2 2 2 Smith January
3 3 3 Johnson December
4 4 4 Johnson December
Smith and January are coming from example workbook1 (table1.xlsb) and Johnson and December are coming from Table2.xlsb.
what is important that name ranges are in the same worksheet as table and whole loyout is tricky:
How can i do this?
Can you please help?
Best,
Jacek


November 8, 2013

You have already a topic here: https://www.myonlinetraininghu.....on-the-fly
The following expression works with both tables and named ranges:
Source= Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]
Source= Excel.CurrentWorkbook(){[Name="TableName"]}[Content]
A new topic will not give you a different solution 🙂 , that's just the way things work in PQ.


February 1, 2019

Sorry,
this is not helpful.
i am merging multiple workbooks together and want to get exactly what i wrote.
What power query will be for this?
Named ranges what you are referring working for specific workbook (the same where power query is).
Here i want to use GetFolder method to merge all data but from external workbooks (merge tables plus named ranges all together in one query).
Please help anybody,
Jacek
I am attaching workbooks


February 1, 2019

Ok Guys,
maybe will be useful for you, i solved the issue (with help from outside)
AllFiles - first query:
let
Source = Folder.Files("C:\Users\Luke\Documents\Power Query\Files"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, 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")))
in
#"Expanded Table Column1"
retriving named range:
let
Source = AllFiles,
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1"}, {"Data.Column1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Name] = "R_Surname")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Data.Column1", "Nazwisko"}})
in
#"Renamed Columns"
retiving table:
let
Source = AllFiles,
#"Filtered Rows2" = Table.SelectRows(Source, each ([Name] = "Sheet1")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Column1] <> null and [Data.Column1] <> "Month" and [Data.Column1] <> "Surname"))
in
#"Filtered Rows"
and last step is just to merge queries 🙂
Best wishes for all Power Query developers!,
Jacek
1 Guest(s)
