Forum Discussion

Copper Contributor

Jan 06, 2020

Solved

(POWER QUERY) Add cell reference to a column

Hello famz, Please Excel Famz, i'm having issue adding a cell reference to a column on Power Query. Background the picture attached to this post is a set of Truck names (BETA, SONIA, RICKET...

Jan 06, 2020

The only difference is the Source where you are using from Table/Range in Get & Transform Data.

The code will look something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1],"(") then null else [Column1]),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"})
in
    #"Filled Up"

After you get your data in Power Query:

Filter out null
Add Custom Column
Use Table.FillUp

You probably won't want to include Rows where the text is 'Model Description','WEEK', or 'POOL TRUCK' .

Kelue Menanya

Copper Contributor

Jan 06, 2020

ChrisMendoza Thanks alot.

Please, here is a sample of the raw data on excel, can you re perform the solution on the excel file and send back. It will greatly aid my understanding.

Thanks.

MODEL DESCRIPTION.xlsx14 KB

ChrisMendoza

Iron Contributor

Jan 06, 2020

Kelue Menanya -

The only difference is the Source where you are using from Table/Range in Get & Transform Data.

The code will look something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([Column1],"(") then null else [Column1]),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"})
in
    #"Filled Up"

After you get your data in Power Query:

Filter out null
Add Custom Column
Use Table.FillUp

You probably won't want to include Rows where the text is 'Model Description','WEEK', or 'POOL TRUCK' .

Kelue Menanya
Copper Contributor
Jan 06, 2020
Wow! This is exactly what I'm trying to achieve