none
Replace all but first occurrence of a value RRS feed

  • Question

  • Can anyone advise me here.

    I have a project cost and budget table and I need the budget to appear only on the first appearance of the project, as below:

    Before:

    Project Budget Cost Country
    121212 15 work Brazil
    121314 32 travel Argentina
    121416 97 work Argentina
    121819 46 travel Uruguai
    121212 15 work Bolivia
    129821 25 travel Peru
    13459898 63 work Paraguai
    13456873 45 work Colombia
    13948501 89 travel Chile
    13456873 45 work Venezuela

    After:

    Project Budget Cost Country
    121212 15 work Brazil
    121314 32 travel Argentina
    121416 97 work Argentina
    121819 46 travel Uruguai
    121212 - work Bolivia
    129821 25 travel Peru
    13459898 63 work Paraguai
    13456873 45 work Colombia
    13948501 89 travel Chile
    13456873 - work Venezuela

    Is it possible to do it using power query?

    Wednesday, August 28, 2019 12:44 AM

Answers

  • Hi Victor,

    If I understand your needs correctly....try this code below

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Grouped Rows" = Table.Group(#"Added Index", {"Project"}, {{"tbl", each Table.ReplaceValue(Table.AddIndexColumn(_, "Idx",0,1), 0, each if [Idx]>0 then null else [Budget], (a,b,c) => c, {"Budget"}), type table}}),
        Combine = Table.Combine(#"Grouped Rows"[tbl]),
        RestoreOriginalOrder = Table.Sort(Combine,{{"Index", Order.Ascending}}),
        Result = Table.RemoveColumns(RestoreOriginalOrder,{"Index", "Idx"})
    in
        Result

    Thursday, August 29, 2019 8:50 AM

All replies

  • yes it is possible, you can do it with the gui: use the function Table.Distinct on your column Budget.

    Table.Distinct

    Wednesday, August 28, 2019 4:45 AM
  • Hi Anthony,

    I think I was not clear. I want to preserve all rows, but erase only the value of budget (Replacing with "-", 0, or anything else) if a PROJECT has appeared above. (show the budget of the project only in the first line it appear).

    Ragarding the example, in the output, the project 121212 appear twice but the budget appear only the first time the project shows up (the same goes for project 13456873).

    I tried to use Table.Distinct but it removed all rows with budget duplicated, but there are other relevant informations in those rows, therefore I need to remove only the budget value.

    I hope you got my problem now, and I hope even more that you can help me find a solution!

    Wednesday, August 28, 2019 9:04 PM
  • Try the following code.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        addedIndex = Table.AddIndexColumn(Source, "Sort Index", 0, 1),
        groupedRows = Table.Group(addedIndex, {"Project"}, {{"Table", each _, type table}}),
        retainedFirstRow = Table.TransformColumns(groupedRows, {"Table", each Table.FirstN(_, 1)}),
        combinedTables = Table.Combine(retainedFirstRow[Table]),
        resortedRows = Table.Sort(combinedTables,{{"Sort Index", Order.Ascending}}),
        removedColumn = Table.RemoveColumns(sortedRows,{"Sort Index"})
    in
        removedColumn

    Thursday, August 29, 2019 12:40 AM
  • Hi Victor,

    If I understand your needs correctly....try this code below

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Grouped Rows" = Table.Group(#"Added Index", {"Project"}, {{"tbl", each Table.ReplaceValue(Table.AddIndexColumn(_, "Idx",0,1), 0, each if [Idx]>0 then null else [Budget], (a,b,c) => c, {"Budget"}), type table}}),
        Combine = Table.Combine(#"Grouped Rows"[tbl]),
        RestoreOriginalOrder = Table.Sort(Combine,{{"Index", Order.Ascending}}),
        Result = Table.RemoveColumns(RestoreOriginalOrder,{"Index", "Idx"})
    in
        Result

    Thursday, August 29, 2019 8:50 AM
  • Hi Bill,

    Does your version provide a performance improvement, or is it just more compliant with the requirement?

    Thursday, August 29, 2019 1:52 PM