Copy cell in power query to multiple cells

[Dhulem]

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 copy Alpha for the next 57 cells and so on. I haven't found a way to tie the company which is Beta, Alpha etc... to each of my questions in column B. Not sure if this is even possile any help would be much appriciated. 

[v-yalanwu-msft · Accepted solution]

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.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dA7DgIxDEXRraDUFMR+ZoYSsYmBaAoWwKdl90i+oGQFVDS29CKfWG6tnB635/X+2iznS1m3rdSsltWzZoms+x7MWQ89qExWGyLRYogm2twj29HqEPGzMW/M2/C7MW/87wiO4J/lh+0dwRF8Gl4QhCAEIQhBbCEMYQhD7CGUQAmUQAmUQAmUQPke//g//U9Pv74B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Organization = _t]),
    #"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]

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]

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]

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]

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]

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

[Dhulem]

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?