Copy First row Text into all column

[Jamiah Abdullah]

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

[#2 · Accepted solution]

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.