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

Thanks, i believe that this will work. I assume "7dA7DgIxDEXRraDUFMR+ZoYSsYmBaAoWwKdl90i+oGQFVDS29CKfWG6tnB635/X+2iznS1m3rdSsltWzZoms+x7MWQ89qExWGyLRYogm2twj29HqEPGzMW/M2/C7MW/87wiO4J/lh+0dwRF8Gl4QhCAEIQhBbCEMYQhD7CGUQAmUQAmUQAmUQPke//g//U9Pv74B" is the source correct?

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

When 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

  • Dhulem's avatar
    Dhulem
    Regular Visitor
    4 years ago

    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?

     

     

  • 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
      Most 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.