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

 

 

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.

8 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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'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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.