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

Resources