Power Query: How to insert a Column using the Left(Right Function based on A4

0
votes

Is there a way to Add a column, in Power Query, by referencing data in a specific cell?

I want to take the text from "A4", use a Left(Right function, and add that to a new column.

My VBA macro is: "Latest 4 Wks - Ending " & Left(Right(.Range("A4"), 24), 23)

Data

2
powerquerym

2 Answers

2
votes

I guess you want to do something like that. In a first step you define a named range for A4 which I named cellA4. I then did a load into Powerquery, added an extra column with the part of the text (I used Text.Middle other text function are possible, of course) from the cell and drilled down to the content of the cell. The M-code for that is

let
    Source = Excel.CurrentWorkbook(){[Name="cellA4"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([Column1],23)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    Custom = #"Removed Columns"{0}[Custom]
in
    Custom

Result looks like

enter image description here

Them I just made a table with one column and imported that into Powerquery and added an extra column which just contains the text from cell A4. M-Code is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each cellA4)
in
    #"Added Custom"

Result is

enter image description here

0
votes

Through further research, I found that by adding a Blank Query, I was able to add a column, in Power Query, by referencing data in a specific cell?

Insert BlankQuery Advance Editor

(YourWorkSheet as table ) as text=>
let

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

The bring in the worksheet data After the Source line

#"Added Custom" = Table.AddColumn(Source, "Custom", each Query1(Source))
In
#"Added Custom"