Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

NEW Column length limit in dataflows messing existing linked tables up!

We are working with dataflows and linked entities.

 

After the recent update, it apparently checks for too long column names. This used to be fine, so I have an existing linked table with column names deemed too long now. So a dataflow that uses this linked table goes crazy: saving a dataflow first adds a Rename operation to the linked table and then complains that Linked tables can't be modified.

 

Please help! This all used to work before the update!

 

 

Dataflow

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous - when you link to the dataflow table "PricingReport" and enable load, Power Query will prevent you from making any modifications within the query.   Essentially, the table will need to re-published like-for-like.  Instead, if you disable the load in the original query and then reference it in a second query, you will be able to make changes and load the modifications. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks, but that's not the point. It has to be a linked table,I want to use the functionality.

    But now PowerBI introduces automatic column renamings also on linked tables, which is impossible!

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous - I am sorry I misunderstood the problem.  There appears to be an undocumented rule in Dataflows that limits the length of column name to 80 characters.  I am sorry but you will need to revise the column name lengths below 80 to avoid the Dataflow validation rule and forced truncation.  I have attached examples below.  

      I suggest that you shorten the Column Names, but include the longer name in the Column Descripition in the Power BI datasets.  Unfortunately, I am not aware that this piece of metadata can be passed from the Dataflow to Dataset.

      //"Example" Dataflow

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PTVVwSSxJVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1234567890" = _t])
in
  Source

       

      //"Linked Entity" - this references Example dataflow then changes the name.  The step #"Renamed columns 1"

let
  Source = #"Example",
  //This step was added manually by the developer
  #"Renamed columns" = Table.RenameColumns(Source, {{"1234567890", "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890"}}),
  #"Added custom" = Table.AddColumn(#"Renamed columns", "Attempted Column Length", each Text.Length( Table.ColumnNames(#"Renamed columns"){0}), Int64.Type),
  // This step was added by the Dataflow validation process
  #"Renamed columns 1" = Table.RenameColumns(#"Added custom", {{"123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890", "12345678901234567890123456789012345678901234567890123456789012345678901234567890"}}),
  Custom = Table.AddColumn(#"Renamed columns 1", "Revised Column Length", each Text.Length( Table.ColumnNames(#"Renamed columns 1"){0} ) , Int64.Type)
in
  Custom