none
Changing a range of values RRS feed

  • Question

  • I am using excel and it's power query editor and I am trying to replace multiple different values into one new value. For instance I want to turn values such as 2/1/2018 0:00, 2/2/2018 0:00, 2/4/2018 0:00, and 2/10/2018 0:00 into February 2018. I need to do this for hundreds to thousands of different values so I don't want to just use the replace values command in the Transform tab, If someone can tell me how to change a range of values into one value in the power query editor that may help. I also want to in the power query editor to be able to get rid of duplicate values of still leave empty cells of the values that were duplicated,

    for instance I want to turn
    "AB" "123" "one"
    "AB" "123" "one.5"
    "AB" "234"" two"
    "AB" "345" "three"
    "AB" "345" "three.5"
    "AB" "345" "three.6"

    Into

    "AB" "123" "one"
    " " "    "   "one.5"
    " " "234" " two"
    " " "345" "three"
    " " "    " "three.5"
    " " "    " "three.6". The quotations were to help differentiate each column to whoever is reading this. Essentially I just need these two questions solved,

    Friday, February 1, 2019 6:33 PM

Answers

  • To change multiple values in a column into a single value you can use Table.TransformColumns e.g.

    ReplacedValues = Table.TransformColumns(<table>, {"<column>", each <newValue>})

    where <table> is the name of the table in which you will change the values, <column> is the name of the column in the table that will have the changes, and <newValue> is the constant value for each row.

    Note that in the case of your date example, unless February is the only month in the column, it would be better to format the dates into month and year, e.g. if the name of the column is Date, then

    ReplacedValues = Table.TransformColumns(<table>, {"Date", each Date.ToText(_, "MMMM yyyy")})

    or if the column has date and time values:

    ReplacedValues = Table.TransformColumns(<table>, {"Date", each DateTime.ToText(_, "MMMM yyyy")})

    For your second question, I've used and Excel table with your sample data. The column names are Col1, Col2, and Col3, respectively.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", Int64.Type}, {"Col3", type text}}),
        GroupedRows = Table.Group(ChangedType, {"Col1", "Col2"}, {{"Tables", each _, type table}}),
        ReplacedValues =
            Table.TransformColumns(
                GroupedRows,
                {
                  "Tables",
                  (currTable) =>
                  let
                      SkippedFirstRow = Table.Skip(currTable, 1),
                      ReplacedWithEmptyValues = Table.TransformColumns(SkippedFirstRow, {{"Col1", each "", type text}, {"Col2", each ""}}),
                      FirstRow = Table.Distinct(currTable, {"Col1", "Col2"}),
                      AppendedFirstRow = FirstRow & ReplacedWithEmptyValues
                  in
                      AppendedFirstRow
                } 
            ),
        CombinedTables = Table.Combine(ReplacedValues[Tables])            
    in
        CombinedTables




    Saturday, February 2, 2019 12:30 AM