Is there a way to enter a Text String (by itself) in Table.AddColumn

[Anonymous]

I have a Workbook that I pull from a website monthly, with 11 Worksheets.  Each Worksheet in call "A1" references the Market Name.

Instead of trying to set a named reference for each worksheet everytime I pull the monthly Data, it there a way to add a column with just a "Text String" that will reference the Market Name?

Example:

Worksheet 1: 

Period : Latest 4 Wks - W/E 10/03/20 • Market : Chicago SMM xAOC • Product Share Basis : ICE CREAM

The New Column would need the text: "Chicago SMM xAOC"

Worksheet 2:

Period : Latest 4 Wks - W/E 10/03/20 • Market : Cincinnati SMM xAOC • Product Share Basis : ICE CREAM

The New Column would need the text: "Cincinnati SMM xAOC"

Worksheet 3:

Period : Latest 4 Wks - W/E 10/03/20 • Market : Cleveland/Akron/Canton SMM xAOC • Product Share Basis : ICE CREAM

The New Column would need the text: "Cleveland/Akron/Canton SMM xAOC"

[Jimmy801 · Accepted solution]

Hello @Anonymous 

i suppose your data looks like this

and I don't know what exactly is your goal. But to extract the Market name you could use this function (to be fet with a table from your worksheet - not a table.

(YourWorkSheet as table ) as text=>
let

    SheetCellA1 =YourWorkSheet[Column1]{0},
    SplitByMarket = Text.Split(SheetCellA1, "Market : "){1},
    SplitByProductionShareBasis = Text.Split(SplitByMarket, "Product Share Basis : "){0}, 
    GetMarketName = Text.Start(SplitByProductionShareBasis, Text.Length(SplitByProductionShareBasis)-3)
in
    GetMarketName

Copy paste this code to the advanced editor in a new blank query to create a function. Apply this function after you read the file in a new column, where you feed the function with the content of a worksheet.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

if you want to add this function here, you have to add a new column then write Query1(Source{[Item= "Report1", Kind="Sheet"]}[Data]).

So you don't need to combine all worksheets?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

i suppose your data looks like this

and I don't know what exactly is your goal. But to extract the Market name you could use this function (to be fet with a table from your worksheet - not a table.

(YourWorkSheet as table ) as text=>
let

    SheetCellA1 =YourWorkSheet[Column1]{0},
    SplitByMarket = Text.Split(SheetCellA1, "Market : "){1},
    SplitByProductionShareBasis = Text.Split(SplitByMarket, "Product Share Basis : "){0}, 
    GetMarketName = Text.Start(SplitByProductionShareBasis, Text.Length(SplitByProductionShareBasis)-3)
in
    GetMarketName

Copy paste this code to the advanced editor in a new blank query to create a function. Apply this function after you read the file in a new column, where you feed the function with the content of a worksheet.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 I updated my orignial post to help clarify what I'm trying to do.

Yes the Data is in "A1", but each worksheet has a different Market Name.  If I'm reading your code correctly, it'll only reference the first worksheet that has "Chicago SMM xAOC".  I need to be able to reference all the different worksheets.

[Jimmy801]

Hello @Anonymous 

no, why? In my first screenshot i asked you if your data is looking like this. If yes I gave you a custom function with a worksheet as input that excracts first the cell in the first column and then extracts out of your data the market name with the described logic.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 I must be doing this wrong..

1. I open a new Power Bi window

2. Add the Blank Query (the code you provided)

3. Add a New Source (my workbook/10 worksheets)

This is where I get confused on your directions:

Apply this function after you read the file in a new column, where you feed the function with the content of a worksheet.

4. On the first worksheet "Report 1":

Add Column

Invoke Custom Function

Change Function query to "Query1"

The Report1 worksheet is not available to choose from..

[PhilipTreacy]

Hi @Anonymous 

Yes, enter the text into a cell in the workbook then create a table from that cell.  You can now reference that table in your query and get the text string from it.

So if your table is called MarketName you can get the text from the single cell in that table using

= Excel.CurrentWorkbook(){[Name="MarkteName"]}[Content]{0}

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@PhilipTreacy I updated my original post to help clarify.  Each worksheet, of the 10 worksheets, has a different Market Name.  I need to add a column with the corresponding market name.