locked
Returning Table Name Text from Table Object RRS feed

  • Question

  • Hi Team

    I'm attempting to add to the Table.Profile function but unfortunately I'm receiving the following error: "We cannot apply operator & to types Table and Text".  It occurs because sSourceTable is a table object and not text.  It's in the last line - any suggestions on how to overcome this?  I'm sure the solution is simple :)

    let
        tSourceTable = VDS, // This will be eventually be the arguement for this script
        Table.Profile = Table.Profile(tSourceTable),
        Table.Schema = Table.Schema(tSourceTable),
        Table.Join = Table.ExpandTableColumn(Table.NestedJoin(Table.Profile,{"Column"},Table.Schema,{"Name"},"NewColumn",JoinKind.LeftOuter), "NewColumn", {"Position", "Kind"}, {"Position", "Kind"}),
        #"Filtered Rows" = Table.SelectRows(Table.Join, each ([Column] = "Action Management")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Unique Records", each if [DistinctCount] < 11 then Text.Combine(Expression.Evaluate(tSourceTable & "[" & [Column] & "]", #shared), ",") else null)
    in
        #"Added Custom"

    Also, on a side note, the above script results in the following deserialization error and some crazy slow performance on large tables: "{"ColumnName","N","Y",null,null,2614,2535,3,51,"text",error [Reason="Expression.Error",Message=@_v[s103]] meta []}},@_v[t1]) meta [Serializer.Truncated = true,]"

    Thanks,
    Simon

    Friday, April 28, 2017 3:23 PM

Answers

  • Without understanding the meaning of the code, this version returns some values in "Unique Records": ;-)

    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Unique Records", each if [DistinctCount] < 11 then Text.Combine(List.Transform(Table.Column(tSourceTable, [Column]), each Text.From(_)), ",") else null)
    


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    • Marked as answer by Simon Nuss Monday, May 1, 2017 11:31 AM
    Saturday, April 29, 2017 5:31 AM

All replies

  • Without understanding the meaning of the code, this version returns some values in "Unique Records": ;-)

    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Unique Records", each if [DistinctCount] < 11 then Text.Combine(List.Transform(Table.Column(tSourceTable, [Column]), each Text.From(_)), ",") else null)
    


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    • Marked as answer by Simon Nuss Monday, May 1, 2017 11:31 AM
    Saturday, April 29, 2017 5:31 AM
  • Thanks Imke!

    As a part of my deliverables, I often send my clients data dictionaries using Table.Profile() as a starting point.  I'm just trying to expand the function to include more useful things, e.g. the distinct list of records, median etc...

    I haven't finished it yet but below is a good starting point for anyone else interested in going down this path:

    (tTarget as table, nDistinctRecordsMax as number) =>
    
        let
            tSourceTable = tTarget, // This will be eventually be the arguement for this script
            Table.Profile = Table.Profile(tSourceTable),
            Table.Schema = Table.Schema(tSourceTable),
            Table.Join = Table.ExpandTableColumn(Table.NestedJoin(Table.Profile,{"Column"},Table.Schema,{"Name"},"NewColumn",JoinKind.LeftOuter), "NewColumn", {"Position", "Kind"}, {"Position", "Kind"}),
            #"Added DistinctList" = Table.AddColumn(Table.Join, "Unique Records", each if [DistinctCount] <= nDistinctRecordsMax then Text.Combine(List.Transform(List.Distinct(Table.Column(tSourceTable, [Column])), each Text.From(_)), ", ") else ">" & Text.From(nDistinctRecordsMax) & " records")
        in
            #"Added DistinctList"

    Thanks,
    Simon


    • Edited by Simon Nuss Monday, May 1, 2017 11:47 AM
    Monday, May 1, 2017 11:45 AM
  • Imke,  I trying to construct a function with 2 parameters, a table reference and a text value.  How would you reference the table for Table.SelectColumns() and the name of the same table in Table.NestedJoin() ?  Below Table.NestedJoin() fails because it requires as text the name of a table, rather than an identifier to the table itself - as shown below...

    SourceTable as table, OpenCloseDateDescriptor as text) =>

    let #"Removed Other Columns" = Table.SelectColumns(SourceTable,{"Value", "Index","Row"}), // then a few steps later... #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns1",{"Row"},SourceTable,{"Row"},SourceTable,JoinKind.LeftOuter),

    As a work around I could ask for the name of the table as a text parameter, but that is a clumsy solution.

    Thanks



    Dan Bliss

    Saturday, March 3, 2018 7:51 PM