Forum Discussion

Dhulem's avatar
Dhulem
Regular Visitor
4 years ago
Solved

Copy cell in power query to multiple cells

Hi all, new to power query and ran into a snag. I am trying to copy "Company -- Beta" from the image below for the next 57 cells. Then at cell 58 I have a new company name for example Alpha and then ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

Dhulem's avatar
Dhulem
Regular Visitor
4 years ago

I went into the advanced editor to grab the source:

 

"

let
Source = Folder.Files("C:\Users\dhulem\Desktop\Scrubbed Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Source.Name", "Column7", "Column8", "Column9"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Organization", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"21 Beta P 23-11 Control Activities - IT and Risk Environment Questionnaire SGB21 - Copy.xlsx", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column2", "Questions"}, {"Column3", "Yes"}, {"Column4", "No"}, {"Column5", "Comment"}},
#"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
#"Renamed Columns""

 

 

 

***** I only used the follwoing that you provided because I already have the source. However, I get an error "the name 'Added Index' wasn't recognized. Make sure ites spelled right.

 

 

"" #"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"})""

 

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

In

#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type)

Replace Source with #"Renamed Columns"

(This need to refer to previous step)

Hence, your complete code would be

let
Source = Folder.Files("C:\Users\dhulem\Desktop\Scrubbed Data"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Source.Name", "Column7", "Column8", "Column9"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Organization", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"21 Beta P 23-11 Control Activities - IT and Risk Environment Questionnaire SGB21 - Copy.xlsx", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column2", "Questions"}, {"Column3", "Yes"}, {"Column4", "No"}, {"Column5", "Comment"}},
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "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
#"Renamed 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

  • Dhulem's avatar
    Dhulem
    Regular Visitor
    4 years ago

    Still getting expression.error: The name 'Added Index' wasn't recognized. Make sure it's spelled correctly.