none
Use saved value as value in a Table function RRS feed

  • Question

  • Hi everyone

    Apologies for the newbie question but I searched, searched and tried various options but can't make it to work. The following function does what I need:

    // BEGIN Function fnReorderTblColumns
    fnReorderTblColumns = (Tbl as table) as table =>
       let
           #"Divisor" = (Table.ColumnCount(Tbl) -1) / 3,
           SavedValue = Tbl{0}[Name],
           #"TableSchema" = Table.RemoveRows(
               Table.SelectColumns(Table.Schema(Tbl),{"Name","Position"}),0,1),
           #"AddCustomOrder" = Table.AddColumn(
                TableSchema, "Order", each if Number.Mod([Position],#"Divisor") = 0
                                          then #"Divisor"
                                          else Number.Mod([Position],#"Divisor")),
           #"SortCustomOrder" = Table.InsertRows(
               Table.Sort(#"AddCustomOrder",{{"Order", Order.Ascending}, {"Position", Order.Ascending}}),0,{[Name = "ID Log", Position = 0, Order = 0]}),
           #"TblCol1ToList" = Table.ToList(Table.SelectColumns(#"SortCustomOrder","Name")),
           #"Out_fnReorderTblColumns" = Table.ReorderColumns(Tbl,#"TblCol1ToList")
        in
           #"Out_fnReorderTblColumns",
    // END Funtion fnReorderTblColumns

    However what I bolded above ("ID Log") is hard-coded and I would like to replace it with the SavedValue (2nd row of the function). I have already verified that Tbl{0}[Name] returns the expected value and type (text in this case) but can't find the correct syntax to make in work in the Table.InsertRows... call

    Any suggestion will be very much appreciated
    Thanks in advance

    Wednesday, March 21, 2018 7:16 PM

Answers

  • @Igor,

    My bad :-( When I checked SavedValue content and type before posting that was outside of the function. Testing the functions steps as a regular regular "procedure" I found my mistake: the SavedValue step was too early in the function + not looking at the correct Table. Problem fixed as follow:

        // BEGIN Function fnReorderTblColumns
        fnReorderTblColumns = (Tbl as table) as table =>
            let
                #"Divisor" = (Table.ColumnCount(Tbl) -1) / 3,
                #"TableSchema" = Table.SelectColumns(Table.Schema(Tbl),{"Name","Position"}),
                    SavedValue = #"TableSchema"{0}[Name],
                #"RemoveRow0" = Table.RemoveRows(#"TableSchema",0,1),
                #"AddCustomOrder" = Table.AddColumn(#"RemoveRow0", "Order",
                    each if Number.Mod([Position],#"Divisor") = 0
                         then #"Divisor"
                         else Number.Mod([Position],#"Divisor")),
                #"SortOrder"=Table.Sort(#"AddCustomOrder",{{"Order", Order.Ascending}, {"Position", Order.Ascending}}),
                #"InsertRow0" = Table.InsertRows(#"SortOrder",0,{[Name = SavedValue, Position = 0, Order = 0]}),
                #"TblCol1ToList" = Table.ToList(Table.SelectColumns(#"InsertRow0","Name")),
                #"Out_fnReorderTblColumns" = Table.ReorderColumns(Tbl,#"TblCol1ToList")
            in
                #"Out_fnReorderTblColumns",
        // END Funtion fnReorderTblColumns
    Thanks again for your interest
    Best regards

    • Marked as answer by Lz._ Thursday, March 22, 2018 8:49 AM
    Thursday, March 22, 2018 7:57 AM

All replies

  • Does this work? Replacing "ID Log" with SavedValue

    fnReorderTblColumns = (Tbl as table) as table => let #"Divisor" = (Table.ColumnCount(Tbl) -1) / 3, SavedValue = Tbl{0}[Name], #"TableSchema" = Table.RemoveRows( Table.SelectColumns(Table.Schema(Tbl),{"Name","Position"}),0,1), #"AddCustomOrder" = Table.AddColumn( TableSchema, "Order", each if Number.Mod([Position],#"Divisor") = 0 then #"Divisor" else Number.Mod([Position],#"Divisor")), #"SortCustomOrder" = Table.InsertRows( Table.Sort(#"AddCustomOrder",{{"Order", Order.Ascending}, {"Position", Order.Ascending}}),0,

    {[Name = SavedValue, Position = 0, Order = 0]}), #"TblCol1ToList" = Table.ToList(Table.SelectColumns(#"SortCustomOrder","Name")), #"Out_fnReorderTblColumns" = Table.ReorderColumns(Tbl,#"TblCol1ToList") in #"Out_fnReorderTblColumns",


    Wednesday, March 21, 2018 7:30 PM
  • Ciao Igor,

    {[Name = SavedValue, Position = 0, Order = 0]}),

    That's what I tried first but this doesn't work :-((. Thanks for trying to help - highly appreciated.

    Cheers

    Wednesday, March 21, 2018 10:36 PM
  • Can you share the error message? Or a sample input table?
    Thursday, March 22, 2018 6:58 AM
  • @Igor,

    My bad :-( When I checked SavedValue content and type before posting that was outside of the function. Testing the functions steps as a regular regular "procedure" I found my mistake: the SavedValue step was too early in the function + not looking at the correct Table. Problem fixed as follow:

        // BEGIN Function fnReorderTblColumns
        fnReorderTblColumns = (Tbl as table) as table =>
            let
                #"Divisor" = (Table.ColumnCount(Tbl) -1) / 3,
                #"TableSchema" = Table.SelectColumns(Table.Schema(Tbl),{"Name","Position"}),
                    SavedValue = #"TableSchema"{0}[Name],
                #"RemoveRow0" = Table.RemoveRows(#"TableSchema",0,1),
                #"AddCustomOrder" = Table.AddColumn(#"RemoveRow0", "Order",
                    each if Number.Mod([Position],#"Divisor") = 0
                         then #"Divisor"
                         else Number.Mod([Position],#"Divisor")),
                #"SortOrder"=Table.Sort(#"AddCustomOrder",{{"Order", Order.Ascending}, {"Position", Order.Ascending}}),
                #"InsertRow0" = Table.InsertRows(#"SortOrder",0,{[Name = SavedValue, Position = 0, Order = 0]}),
                #"TblCol1ToList" = Table.ToList(Table.SelectColumns(#"InsertRow0","Name")),
                #"Out_fnReorderTblColumns" = Table.ReorderColumns(Tbl,#"TblCol1ToList")
            in
                #"Out_fnReorderTblColumns",
        // END Funtion fnReorderTblColumns
    Thanks again for your interest
    Best regards

    • Marked as answer by Lz._ Thursday, March 22, 2018 8:49 AM
    Thursday, March 22, 2018 7:57 AM