Column content = Table Name or Query Name

[deathseeker]

Hello!!!

I need to merge three tables, one for each year (2015, 2016 and 2017), and continue to do so in the next years. And for that, i need a personalized column in the respective year. 

Is there a Power Query M function that allows the merged query to pull the "table name" or "query name" for all the cells in the new column?

Hoping that is not to much confusing.

Example:

SALES --- CLIENT ---- YEAR

100USD --- ALFA ---- 2016

150USD --- BETA --- 2016

125USD --- DELTA --- 2017

(personalized column in orange)

Best regards and thanks in advance

[MarcelBeug · Accepted solution]

Remark: according to your example, you are appending tables, not merging tables.

There is no function to get the name of your table.

But there is still a way to achieve what you want. It requires a deep dive into the realms of Power Query though.

Suppose your table names are Year2015, Year2016 etcetera.

Create a table "Years". e.g. for the years 2015-2030 (you may have additional years in this table for which you have no data yet):

Table.FromList({2015..2030}, each {_}, type table[Year = Int64.Type])

(this is the entire query code, so no let..in structure).

Now create the following query that creates the required result:

let
    Source = Years,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("Year"&Text.From([Year]), #sections[Section1])),
    RemovedErrors = Table.RemoveRowsWithErrors(AddedTables, {"Data"}),
    ReorderedColumns = Table.ReorderColumns(RemovedErrors,{"Data", "Year"}),
    AddedColumnNames = Table.AddColumn(ReorderedColumns, "ColumnNames", each Table.ColumnNames([Data])),
    ColumnNames = List.Distinct(List.Combine(AddedColumnNames[ColumnNames])),
    ExpandedData = Table.ExpandTableColumn(AddedColumnNames, "Data", ColumnNames),
    RemovedColumnNames = Table.RemoveColumns(ExpandedData,{"ColumnNames"})
in
    RemovedColumnNames

This code uses Expression.Evaluate to turn table names into tables; this function requires an environment record, otherwise the tables are unknown objects when the expression is evaluated. The keyword #sections is used to get the objects from the current environment (Excel or pbix file), which returns a record with 1 field "Section1", which in turn is a record with the objects from the current environment, including your tables.

The code is dynamic with regard to changing table structures over the years, i.e. the tables may have different columns.

[jmdh]

By the way, do you have a recommandation for a good Reference source about the M Language?

[Anonymous]

@jmdh - not a pure reference source, but I do recommend Chris Webb's Power Query for Power BI and Excel. Has some very useful examples and gives a good understanding to Power Query's functional language, M Query.

[deathseeker]

hmmmm... Thank you so much for the help, but it seems like something above my pay check in Power Query, at the moment...

I will for sure get back to this post, that I really thank you, but for now, I will simply add manually a personalized column to each table. I will not have the important and valuable dynamic column, but I have to go LEAN at this moment.

Sorry about the "merging" mistake, but I am using Excel in my native language (I really have to change it) and wrongly made the translation.