Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dmarics28
Frequent Visitor

Trying to create a new column based on the query or source file name.

Hi Guys,

I am trying to create a simple new column in my dataset. But I need the data to be the Query name.
What is the formula for Query Name? Or Source File name? Thanks in advance.

 

dmarics28_0-1595980751811.png

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @dmarics28 ,

 

Try to change the code of your Append query in the Advanced Editor.

let
    Source = Table.Combine({Table.AddColumn(#"query A", "source query", each "query A")
                            , Table.AddColumn(#"query B", "source query", each "query B")
                            , Table.AddColumn(#"query C", "source query", each "query C")})
in
    Source

query.PNG

 

BTW, .pbix file attached.

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

17 REPLIES 17
Icey
Community Support
Community Support

Hi @dmarics28 ,

 

Try to change the code of your Append query in the Advanced Editor.

let
    Source = Table.Combine({Table.AddColumn(#"query A", "source query", each "query A")
                            , Table.AddColumn(#"query B", "source query", each "query B")
                            , Table.AddColumn(#"query C", "source query", each "query C")})
in
    Source

query.PNG

 

BTW, .pbix file attached.

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey 
Thanks that did work.


However, i have about 20 or 30 data sources, and typing each one is very time consuming.

And need to edit the code each time i need to add another source.

If somehow there was a formula instead on manually typing source name in the advanced editor, that would help.

But thanks to everyone who put some ideas forward on this.

ziying35
Impactful Individual
Impactful Individual

@dmarics28 

// out
let
    Source = Record.ToTable(Record.RemoveFields(#sections[Section1],"out")),
    expd = Table.ExpandTableColumn(Source, "Value", {"Value"}, {"Value.1"})
in
    expd

// first
let
    Source = Table.FromValue({1..5})
in
    Source

// second
let
    Source = Table.FromValue({1..5})
in
    Source

Here are three queries combined for a demonstration to see if this is what you want, That's as far as I can go

ziying35
Impactful Individual
Impactful Individual

@dmarics28 

the query ribbon → Add Column → Custom Column →

 

Text.Combine(Record.FieldNames(Record.RemoveFields(#sections[Section1], "Append1")),", ")

 

Append1 is the name of the current query, Since Sections also display the name of the current query, the name of the current query should be removed

@ziying35 
No, this is no good either. This is just giving me all the query names concatenated together seperated by "," All in the same cell.


I need the new column to have the name of the query where the appended data originated from.

BA_Pete
Super User
Super User

Hi @dmarics28 ,

 

1) In Power Query, go to New Source>Blank Query>Advanced Editor and paste this code over the default code:

let
    Source = #sections,
    Section1 = Source[Section1],
    convertTable = Record.ToTable(Section1)
in
    convertTable

This will load a table with all components of your Power Query setup.

 

2) Filter the [Name] column to include only the names of the tables that you want to append. Once you have done this, you will see that the dropdown button in the column header changes to an expand button:

dmarics1.PNG

 

3) Expand this as you would after a Merge/Join including all columns that are the same in both queries:

dmarics2.PNG

 

4) You will then have an appended table which includes the source query name in each:

dmarics3.PNG

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




For me, this does perfectly the job. Thank you

@BA_Pete 
Almost! : ) So close!

When i go to apply it to the model, i get this error.

 

dmarics28_0-1596008000455.png

 

@dmarics28 ,

 

Yes, unfortunately #sections and #shared are Power Query environment only. I should have checked your use-case first.

In terms of carrying the appended query names over, on balance of time/effort, I see your options as follows:

 

1) Manually add a column into each of your PRE-APPENDED tables, like ="myTableName1", ="myTableName2" etc. Ensure these columns have the same field name and data type in each table and the append process will bring them in correctly the same as all the other data columns.

2) Use your data source to create a 'Source Name' table that is bundled with the main data table. For example, in Excel you could create another tab that just contains the name that you want the source to be called. Bring this table in with the main data and then use it as a source to reference in an added column in Power Query. In reality, this essentially achieves the same as option 1), but in a far more roundabout way.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




ziying35
Impactful Individual
Impactful Individual

@dmarics28 

Do you want to get a list of all query names for the current file in a new column? Or a list collection that references all queries?

@ziying35 
I want to create a new column in my appended query.
The new column cells need to reference just the query name that the original data came from.

Can this be done?

ziying35
Impactful Individual
Impactful Individual

@dmarics28 

 

= Table.AddColumn(PreviousStep, "query names", (r)=>Record.FieldNames(Record.RemoveFields(#sections[Section1], "Append1")))

 

Append1 is the name of the current query, Does it help you to look at this

@ziying35 
Nope. No good.
Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly.

ziying35
Impactful Individual
Impactful Individual

Replace PreviousStep with the name of the previous step in your query, such as Source

@ziying35 
Almost! : )
Now im getting a link to the source instead of just plain text name.

 

2020-07-29_16-33-42.png

mahoneypat
Employee
Employee

You can just put = Query1    (the name of your query).  This will add the entire query into a table on each row.  Is that your desired outcome?

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


No, That didnt work.

That returned an error.

Expression.Error: The name 'query1' wasn't recognized. Make sure it's spelled correctly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors