(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...

BI & Data Analysis

excel

power bi

ChrisMendoza

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' .

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9RD4IgFIX/yp1PuGVBZvVKgsgQcYCz5fz/fyNCU1sv7HK+c8/OHcdk4Fwl02FMOmMa8LYv5298qmuRZ6wC5GwK5Jg3oP0GdAB9tQC6gksAaOAfvVj1B/d0l3vHOK5jnC4u0JKBsJTxzSF+HbUU9c7iTCt3ma3rSJFppgCpUMqxb9cZPCMQESydrCwV91vCy/rzLeu4GgARnAI9+X+dRB0aN59lRB+m6Q0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"MODEL DESCRIPTION" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODEL DESCRIPTION", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([MODEL DESCRIPTION] <> "")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if Text.Contains([MODEL DESCRIPTION],"(") then null else [MODEL DESCRIPTION]), #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}) in #"Filled Up"

Forum Discussion

(POWER QUERY) Add cell reference to a column

Resources