Forum Discussion

BenGreenwood's avatar
BenGreenwood
Copper Contributor
Mar 03, 2020
Solved

Repeat a value for every matching cell in another column?

Hi folks!

 

Could someone please, please advise me on how to do the following?

 

I have data from a couple of CSV files brought in by the Get Data > Folder method. In the resulting table, I need to repeat the quote number for each matching ticket number in a situation like in the table below.

 

Ticket numberQuote number
343434qte111
343434 
898989 
212121qte222
212121 

 

should become:

 

Ticket numberQuote number
343434qte111
343434qte111
898989 
212121qte222
212121qte222

 

so that the Quote number repeats for matching ticket numbers, but only for ticket numbers where a quote number is present.

 

Happy to create a new column for this, or for a solution that deals with the issue in Power Query.

 

Any ideas, my lovely geniuses?

 

Ben

excel
Formulas and Functions
  • Savia's avatar
    Savia
    Mar 03, 2020
    I'm not suggesting getting rid of any rows in your main table. Surely the rows with no quote number currently are nulls - I'm suggesting duplicating your current query and reducing to one quote number per ticket number so you can use that as a lookup table to fill in the gaps on your main query.

8 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 03, 2020

    BenGreenwood 

    As variant, with grouping and bit of coding

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupRows = Table.Group(
        Source,
        {"Ticket number"},
        {
            {"Quote number",
             each List.ReplaceValue(
                 [Quote number],
                 null,
                 List.First([Quote number]),
                 Replacer.ReplaceValue
                )
            }
        }
    ),
    ExpandQuoteNumber = Table.ExpandListColumn(
        GroupRows,
        "Quote number"
    )
in
    ExpandQuoteNumber

  • Savia's avatar
    Savia
    Iron Contributor
    Mar 03, 2020
    Load a second copy of the table and filter out all the null rows. Save that as a connection. Then go back to the original query and merge with the new query and expand out the Quote number column - should match with every possible row. Then delete the original Quote no column.
    • BenGreenwood's avatar
      BenGreenwood
      Copper Contributor
      Mar 03, 2020

      Hi Savia ,

       

      I think I get what you are suggesting, but I don't have any null rows. I need to keep all those duplicated ticket numbers. There is a primary key elsewhere in the table.

      • Savia's avatar
        Savia
        Iron Contributor
        Mar 03, 2020
        I'm not suggesting getting rid of any rows in your main table. Surely the rows with no quote number currently are nulls - I'm suggesting duplicating your current query and reducing to one quote number per ticket number so you can use that as a lookup table to fill in the gaps on your main query.

Resources