Forum Discussion

Jamiah Abdullah's avatar
Jamiah Abdullah
Copper Contributor
Sep 29, 2018

Copy First row Text into all column

Hi ,

 

I have below Table :

Folders : renmi_out

Data   Date  Maturity

1         25/09  26/09

2         25/09  26/09

3         25/09  26/09

 

 

How do I copy the FOLDERS: renmi-out to all data in a new column using power query. I have about 50,000 data and some of the folders is not the same.

 

Thanks

BI & Data Analytics
Power Query
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 30, 2018

    Hi Jamiah,

     

    For me it's not clear how exactly is your data structured and what exactly is the form of the resulting table. If something like this

    you may first add name with your source data , let say =Sheet1!$A$1:$C$100000 as MySheet and generate PQ script as attached

    let
    Source = Excel.CurrentWorkbook(){[Name="MySheet"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Folders", each if Text.Start([Column1],7) = "Folders" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Folders"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each
        not Text.StartsWith([Column1], "Folders") and not Text.StartsWith([Column1], "Data")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",
        {{"Column1", "Data"}, {"Column2", "Date"}, {"Column3", "Maturity"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{
        {"Data", Int64.Type},
        {"Date", type date},
        {"Maturity", type date},
        {"Folders", type text}})
in
    #"Changed Type"

    The idea is to add new column which has the value from first column if the text in it starts from "Folders", otherwise null and fill it down. Rest is cosmetic.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 30, 2018

    Hi Jamiah,

     

    For me it's not clear how exactly is your data structured and what exactly is the form of the resulting table. If something like this

    you may first add name with your source data , let say =Sheet1!$A$1:$C$100000 as MySheet and generate PQ script as attached

    let
    Source = Excel.CurrentWorkbook(){[Name="MySheet"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Folders", each if Text.Start([Column1],7) = "Folders" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Folders"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each
        not Text.StartsWith([Column1], "Folders") and not Text.StartsWith([Column1], "Data")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",
        {{"Column1", "Data"}, {"Column2", "Date"}, {"Column3", "Maturity"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{
        {"Data", Int64.Type},
        {"Date", type date},
        {"Maturity", type date},
        {"Folders", type text}})
in
    #"Changed Type"

    The idea is to add new column which has the value from first column if the text in it starts from "Folders", otherwise null and fill it down. Rest is cosmetic.

Resources