Forum Discussion
Copy cell in power query to multiple cells
Hi, Dhulem ;
You could create a condition column , then use fill down.
2.fill down then delete original column.
The final show:
let Source = Folder.Files("C:\Users\Administrator\Desktop"), #"C:\Users\Administrator\Desktop\_Scrubbed Data xlsx" = Source{[#"Folder Path"="C:\Users\Administrator\Desktop\",Name="Scrubbed Data.xlsx"]}[Content], #"Imported Excel Workbook" = Excel.Workbook(#"C:\Users\Administrator\Desktop\_Scrubbed Data xlsx"), Sheet1_Sheet = #"Imported Excel Workbook"{[Item="Sheet1",Kind="Sheet"]}[Data], #"Added Conditional Column" = Table.AddColumn(Sheet1_Sheet, "Custom", each if [Column1] = "Beta" then [Column1] else if [Column1] = "Alpha" then [Column1] else null), #"Promoted Headers" = Table.PromoteHeaders(#"Added Conditional Column", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Organization", type text}, {"Column2", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Column2"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Organization"}) in #"Removed Columns"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Most Valuable ProfessionalWhen you pull in your data, you get a source line generated which should be copied here or vice versa steps after source from my query can be copied into your query.
Let's assume that you pull your data from Excel, so source line generated is
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
Then query will become
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Custom1 = Table.ReplaceValue(#"Added Index",each [Organization],each if Number.Mod([Index],57)=0 then [Organization] else null,Replacer.ReplaceValue,{"Organization"}),
#"Filled Down" = Table.FillDown(Custom1,{"Organization"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"})
in
#"Removed Columns"👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
Not sure if it matters but I am pulling this from a folder with multiple excel files and they are getting appended to one another. Would the source be = folder.files.... as seen below or would it change to the Step-- EXpanded Table Column1?