Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
1 month ago
Solved

Data format inconsistent between table in source dataflow and linked dataflow

I have a custom column calculated like so in the source dataflow:     For example, a SP_DURATION with a value of 32 would return "4.00 MD".     I have this table linked into another...
Need Help
  • olimilo's avatar
    olimilo
    1 month ago

    No additional Changed Type steps were automatically added in the linked table.

     

    I ended up deleting the linked table and re-adding it again after applying the changes which solved the problem.

pcoley's avatar
pcoley
Super User
1 month ago

olimilo 

this is a known behavior with linked entities in Power BI Dataflows. The exact string formatting from Number.ToText(..., "F2") is not always reliably preserved when the table is linked into another dataflow. The downstream dataflow often re-evaluates or stores the text column with higher precision (showing many extra zeros).

 

Please try forcing the exact format in the source dataflow

In your source dataflow (where the custom column AuditMandays is created), add an extra step after the custom column to explicitly enforce the text format:

  1. Select the AuditMandays column.
  2. Go to Transform → Data Type → Text (or right-click → Change Type → Text).
  3. If needed, add a Replace Values or Custom Column step to clean it:
Table.TransformColumns(PreviousStep, {{"AuditMandays", each Text.Trim(_), type text}})
Or force it with:
Table.ReplaceValue(PreviousStep, each [AuditMandays], each 
    if Text.Contains([AuditMandays], "MD") then [AuditMandays] 
    else Number.ToText(Number.Round([SP_SP_DURATION]/8, 2), "F2") & " MD", 
    Replacer.ReplaceValue, {"AuditMandays"})

 

olimilo's avatar
olimilo
Post Prodigy
1 month ago

This is actually mind-boggling to me. At the request of parry2k , I created a dummy query containing sample data and the data from the linked table was showing the intended format:


(Dummy) Source Query from Source dataflow:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZS0lFyVIrViVYyNAMyncBMCyDLGcwyAbJcwKy80pwcIEcpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SP_DURATION = _t, AUDITOR = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"SP_DURATION", Int64.Type}, {"AUDITOR", type text}}),
  #"Added custom" = Table.AddColumn(#"Changed column type", "Custom", each if [AUDITOR] = "" then "0 MD" else Number.ToText([SP_DURATION] / 8) & " MD"),
  #"Transform columns" = Table.TransformColumnTypes(#"Added custom", {{"Custom", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Custom", null}})
in
  #"Replace errors"

 

 

(Dummy) Linked table data:

 

 

I also did the same to the data direct from the source (Salesforce if it matters) and it was displaying correctly:

 

Source query from source dataflow:

let
  Source = ...,
  #"Added custom" = Table.AddColumn(Source, "Custom", each [SP_DURATION] / 8),
  #"Added custom 1" = Table.AddColumn(#"Added custom", "Custom (2)", each Number.ToText([Custom]) & " MD"),
  #"Transform columns" = Table.TransformColumnTypes(#"Added custom 1", {{"Custom", type text}, {"Custom (2)", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Custom", null}, {"Custom (2)", null}})
in
  #"Replace errors"

 

Linked table data:

 

I simplified the query with the issue (ie: removing the columnType parameter of the Table.AddColumn function) and explicitly specifying the data type right after (as you suggested) but the text format getting passed on from the source to the linked table is still the same.