Sep 28 2018
10:53 PM
- last edited on
Jul 12 2019
11:14 AM
by
TechCommunityAP
Sep 28 2018
10:53 PM
- last edited on
Jul 12 2019
11:14 AM
by
TechCommunityAP
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
Sep 30 2018 07:31 AM
SolutionHi 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.
Sep 30 2018 07:31 AM
SolutionHi 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.