How to reference Text in a Cell to create a new column

[Anonymous]

I have a Workbook where I pull data from a website monthly, with multiple worksheets.  Each Worksheet in cell "A4" references the Time Period of the Data pulled.

Example:

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

 I modified a blank query I used before, but it references "A1"

(YourWorkSheet as table ) as text=>
let

    SheetCellA1 =YourWorkSheet[Column1]{0},
    SplitByFrom = Text.Split(SheetCellA1, "'From: "){1},
    SplitByTime = Text.Split(SplitByFrom, "`"){0}, 
    GetTimeFrame = Text.Start(SplitTime, Text.Length(SplitByTime)-1)
in
    GetTimeFrame

How do I change this to reference "A4"

Final result would change:

Current Weeks : 'From: 2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)`

to

2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)

File Example:

OneDrive File 

[AlB · Accepted solution]

Hi @Anonymous 

SheetCellA4 =YourWorkSheet[Column1]{3}

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

Sure, my code is reading from a xlsx but you  have a csv. Like I said, I'd need the actual csv and not the xlsx I downloaded. OR you can try to change the initial code, to adapt it to read from a csv instead of an xlsx. It shouldn't be too difficult. Just create a new query reading from your CSV and see what code PQ generates, then add my code after that. You might have to tweak it a bit

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

The file downloaded from that link is xlsx, not csv?? I do not know if it was xlsx from the beginning or if it is converted to xlsx when I download. Anyway with that xlsx file, you can do the following.

1. Change slightly the function. Just remove the last step since it deletes the last character of the string and that doesn't seem to be what you want:

= (YourWorkSheet as table ) as text=>
let

    SheetCellA4 =YourWorkSheet[Column1]{3},
    SplitByFrom = Text.Split(SheetCellA4, "'From: "){1},
    SplitByTime = Text.Split(SplitByFrom, "`"){0} 
in
    SplitByTime

2. Create another query for the main processing:

let
    Source = Excel.Workbook(File.Contents("C:\Users\cday\OneDrive - udfinc.com\M6 Scorecard\Data Pulls\4 Wk Data.xlsx"), null, true),
    in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
    valueOfInterest_ = Query1(in_Sheet),
    #"Removed Top Rows" = Table.Skip(in_Sheet,16),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Adde column", each valueOfInterest_)
in
    #"Added Custom"

You'll have to review if you want all those columns, since some of them are all nulls. If you want me to have a look at the csv, you need to make sure I can download the csv

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

I updated my original post with the link, and final outcome.

I'm not sure why the extension is showing as xlsx.  I just double checked, and it's saved as .csv

I do want to eliminate the last character of the sting ( ` ).

The blank rows were because I needed to delete confidential data, but I do need them.

I copied your code, the only thing I changed was the extension to ".csv".

let
    Source = Excel.Workbook(File.Contents("C:\Users\cday\OneDrive - udfinc.com\M6 Scorecard\Data Pulls\4 Wk Data.csv"), null, true),
    in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
    valueOfInterest_ = Query1(in_Sheet),
    #"Removed Top Rows" = Table.Skip(in_Sheet,16),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Adde column", each valueOfInterest_)
in
    #"Added Custom"

But I received this error:

[AlB · Accepted solution]

Hi @Anonymous 

SheetCellA4 =YourWorkSheet[Column1]{3}

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB Thanks, I made the adjustment, and tried to add a new column:

= Table.AddColumn(Source, "Custom", each Query1(Source{[Item= "Sheet1", Kind="Sheet"]}[Data]))

But I'm getting an error:

How do I fix this?

[AlB]

@Anonymous 

You seem to be adding a whole table in each of the rows of the column you are creating. That can't work.  What are you trying to do exactly? I'd need to see the whole code (and that of functions if you use any)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

I open a new workbook, and add this Blank Query: 

(YourWorkSheet as table ) as text=>
let

    SheetCellA4 =YourWorkSheet[Column1]{3},
    SplitByFrom = Text.Split(SheetCellA4, "'From: "){1},
    SplitByTime = Text.Split(SplitByFrom, "`"){0}, 
    GetTimeFrame = Text.Start(SplitByTime, Text.Length(SplitByTime)-1)
in
    GetTimeFrame

I then add my Data Source 

Then I want to Add a Column with the Text String that I am pulling from Query1: 

let
    Source = Csv.Document(File.Contents("C:\Users\cday\OneDrive - udfinc.com\M6 Scorecard\Data Pulls\4 Wk Data.csv"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Query1(Source{[Item= "Sheet1", Kind="Sheet"]}[Data]))
in
    #"Added Custom"

So I need to take the A4 text string:

Current Weeks : 'From: 2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)`

And add a Column with only:

2020 PD 09 WK 1 (33) to 2020 PD 09 WK 4 (36)

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you are using an approach of accessing a xlsx-file even you are accessing a csv-file. In your case the Source-step will give you already the final table. So use this code instead

let
    Source = Csv.Document(File.Contents("C:\Users\cday\OneDrive - udfinc.com\M6 Scorecard\Data Pulls\4 Wk Data.csv"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Query1(Source))
in
    #"Added Custom"

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

[AlB]

@Anonymous 

Ah, so Query1 is the function, I assume? Then it looks good. Can you share the csv so that I can have a look on the real thing?  I haven't come across that error message before

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

Correct.  Here you go

PowerQuery