Forum Discussion

Kelue Menanya's avatar
Kelue Menanya
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 & BOGUS) and its model (which are directly above the truck names).

 

Problem

i want to separate the Truck names to a new column,

 

NB: I need this in Power Query and not Excel. I have over 20,000 rows of such data that need to be analysed.

 

cuong 

BI & Data Analysis
excel
power bi
  • ChrisMendoza's avatar
    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:

    1. Filter out null
    2. Add Custom Column
    3. Use Table.FillUp

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

4 Replies

    • ChrisMendoza's avatar
      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:

      1. Filter out null
      2. Add Custom Column
      3. Use Table.FillUp

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

  • ChrisMendoza's avatar
    ChrisMendoza
    Iron Contributor
    Jan 06, 2020

    Kelue Menanya -

    You could use something like:

    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"

    assuming all models contain "(" and no Names contain "("

Resources