Data format inconsistent between table in source dataflow and linked dataflow

[olimilo]

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 dataflow to combine it with another table but the format of the digits vary from its source:

Is it possible to retain the data format from the source table of the source dataflow to the linked table in the other dataflow ?

[v-nmadadi-msft]

Hi @olimilo ,

Thanks for reaching out to the Microsoft Fabric Community forum. 

Open the linked table in the downstream Power query and inspect the applied transformation steps. In particular, check whether a Changed Type step has been automatically added, as this can cause the column to be reinterpreted during ingestion. If the column type differs from the source dataflow (for example, Text being converted to a numeric type), the displayed format may not be preserved even though the underlying value remains unchanged.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[olimilo · Accepted solution]

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.

[parry2k]

@olimilo I'm trying to understand your example code, so it is working, correct?

[parry2k]

@olimilo, can you share the M code and remove any sensitive information from the code? I think something else is going on here. I need to look at the steps to understand the problem. From screenshot, I can see that when you are adding a custom column, you have given the data type as text, which means it doesn't need an extra change type step.

[pcoley]

@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]

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.

[Fenil_Italiya]

Hello, @olimilo 

Could you please mentioned the more details regarding what you wants as result.

Either you wants to get data format of the calculated column in the 1.000000 format or you want anything else if you could explain it in the more details then it would be easy to give the solution.

[olimilo]

It's literally there:

Is it possible to retain the data format from the source table of the source dataflow to the linked table in the other dataflow ?

Now to give a more specific example, if the table from the source dataflow is showing "4.00 MD", that is the exact same value I expect to see in the dataflow referencing that table, as shown in the images.