SOLVED
Home

Copy First row Text into all column

%3CLINGO-SUB%20id%3D%22lingo-sub-264472%22%20slang%3D%22en-US%22%3ECopy%20First%20row%20Text%20into%20all%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264472%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20below%20Table%20%3A%3C%2FP%3E%3CP%3EFolders%20%3A%20renmi_out%3C%2FP%3E%3CP%3EData%26nbsp%3B%20%26nbsp%3BDate%26nbsp%3B%20Maturity%3C%2FP%3E%3CP%3E1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B25%2F09%26nbsp%3B%2026%2F09%3C%2FP%3E%3CP%3E%3CSPAN%3E2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B25%2F09%26nbsp%3B%2026%2F09%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B25%2F09%26nbsp%3B%2026%2F09%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20copy%20the%20FOLDERS%3A%20renmi-out%20to%20all%20data%20in%20a%20new%20column%20using%20power%20query.%20I%20have%20about%2050%2C000%20data%20and%20some%20of%20the%20folders%20is%20not%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-264472%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264630%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20First%20row%20Text%20into%20all%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264630%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamiah%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20me%20it's%20not%20clear%20how%20exactly%20is%20your%20data%20structured%20and%20what%20exactly%20is%20the%20form%20of%20the%20resulting%20table.%20If%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20557px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55033i7CCBDFA8D79AD429%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20first%20add%20name%20with%20your%20source%20data%20%2C%20let%20say%20%3DSheet1!%24A%241%3A%24C%24100000%20as%20MySheet%20and%20generate%20PQ%20script%20as%20attached%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22MySheet%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BColumn1%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%23%22Filtered%20Rows%22%2C%20%22Folders%22%2C%20each%20if%20Text.Start(%5BColumn1%5D%2C7)%20%3D%20%22Folders%22%20then%20%5BColumn1%5D%20else%20null)%2C%0A%20%20%20%20%23%22Filled%20Down%22%20%3D%20Table.FillDown(%23%22Added%20Custom%22%2C%7B%22Folders%22%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows1%22%20%3D%20Table.SelectRows(%23%22Filled%20Down%22%2C%20each%0A%20%20%20%20%20%20%20%20not%20Text.StartsWith(%5BColumn1%5D%2C%20%22Folders%22)%20and%20not%20Text.StartsWith(%5BColumn1%5D%2C%20%22Data%22))%2C%0A%20%20%20%20%23%22Renamed%20Columns%22%20%3D%20Table.RenameColumns(%23%22Filtered%20Rows1%22%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Column1%22%2C%20%22Data%22%7D%2C%20%7B%22Column2%22%2C%20%22Date%22%7D%2C%20%7B%22Column3%22%2C%20%22Maturity%22%7D%7D)%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Renamed%20Columns%22%2C%7B%0A%20%20%20%20%20%20%20%20%7B%22Data%22%2C%20Int64.Type%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Date%22%2C%20type%20date%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Maturity%22%2C%20type%20date%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Folders%22%2C%20type%20text%7D%7D)%0Ain%0A%20%20%20%20%23%22Changed%20Type%22%3C%2FPRE%3E%0A%3CP%3EThe%20idea%20is%20to%20add%20new%20column%20which%20has%20the%20value%20from%20first%20column%20if%20the%20text%20in%20it%20starts%20from%20%22Folders%22%2C%20otherwise%20null%20and%20fill%20it%20down.%20Rest%20is%20cosmetic.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Jamiah Abdullah
Occasional Visitor

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

1 Reply
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

image.png

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies