Log In
Register
Home
February 1, 2019
Offline
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
February 1, 2019
Offline
Anyone?
Jacek
November 8, 2013
Offline
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
Offline
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
November 8, 2013
Offline
You cannot connect to named ranges from external books.
All you can do is to convert the named range into a table with same name, Then query the folder where you have all workbooks.
February 1, 2019
Offline
Thanks.
It is not possible to query named ranges?
And combining tables is only for whole worksheet, not exact range where table is?
How can i do this approach with 2 tables? Can you provide example ?
Best,
Jacek
November 8, 2013
Offline
You cannot connect to named ranges from external books.
Why don't you add a new column in the source table you have, with the formula=NamedRangeName
February 1, 2019
Offline
Hmm this is idea!
But few questions still:
1) i see that power query sees named ranges and not seeing tables:
So still retriving tables equails to retrive whole worksheet?
I think it is possible to retrive somehow named ranges if power query sees them
Best,
Jacek
November 8, 2013
Offline
Ok, seems like it reads named ranges, you can right click the first table and add as a new query, same for second table, drill down until the result of these new queries are text, not lists, then you'll be able to add them as new column formulas in the third table.
February 1, 2019
Offline
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
