Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
A table imported from PDF has negative values denoted with one right bracket, like this "6,964.68)". (The left bracket has been placed in a separate column due to the way Power Bi does text to table translation.)
I'm trying to replace all values containing ")" with a cleaned and negative version of the value. (ie. "6,964.68)" would become "-6,964.68".
Importantly, because this is for a large data set, I'm trying to achieve this:
A: Without creating a helper column and
B. Without referring to specific column names
This code works:
= Table.ReplaceValue(#"Filtered Rows",
each [Column9],
each if Text.Contains([Column9],")")
then "-" & Text.Replace([Column9],")","")
else [Column9],
Replacer.ReplaceText,
Table.ColumnNames(#"Filtered Rows"))
This doesn't:
= Table.ReplaceValue(#"Filtered Rows",
each Table.ColumnNames(#"Filtered Rows"),
each if Text.Contains(Table.ColumnNames(#"Filtered Rows"),")")
then "-" & Text.Replace(Table.ColumnNames(#"Filtered Rows"),")","")
else Table.ColumnNames(#"Filtered Rows"),
Replacer.ReplaceText,
Table.ColumnNames(#"Filtered Rows"))
Solved! Go to Solution.
Hi @powerqueryquest ,
Table.ColumnNames(#"Filtered Rows") returns a list contains all column names, not the value of the columns. So these parts won't work:
Text.Contains(Table.ColumnNames(#"Filtered Rows"),")"),
Text.Replace(Table.ColumnNames(#"Filtered Rows"),")","")
Just add one Index column and use UnPivot and Pivot, then what you want appears:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVRbjsMgDLxK1a9dybL8AuOzVL3/NdZ5EJEmm6r7sx8RMdjDeBh4PO63O3z4PWGpYqBQiNqG1ZITXpHkJKalRgMiGK2XGYYesG9r/jjW3Mtw3o6Q6PY1zH2/5A4sCw38RkzarR4rC4HsirVgay//tCayIfemk50f0KQWILrismYg/U6JwShA95IbEDPGJhPNkpfOjkGtgQZhdIKGei755yb4W5W0kozqlRhLxqUYlp17GUA49WmMVU5iAWmOMvXNSqBUkXttYKlH+GTgu/M3Btc8n3oSKwm2mFkpODtSBxfBelTb2zs7rBnv7JDeEx7toGlGxs0iYyxQ0gazTydXGBgHSsdVjPiPLQLYfIBv2bT0I3TwvOCx3PYOPt326WFh9O2hWR2/Qj9/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
Custom1 = Table.ReplaceValue(#"Unpivoted Other Columns",
each [Value],
each if Text.Contains([Value],")")
then "-" & Text.Replace([Value],")","")
else [Value],
Replacer.ReplaceText,
Table.ColumnNames(#"Unpivoted Other Columns")),
#"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @powerqueryquest ,
Table.ColumnNames(#"Filtered Rows") returns a list contains all column names, not the value of the columns. So these parts won't work:
Text.Contains(Table.ColumnNames(#"Filtered Rows"),")"),
Text.Replace(Table.ColumnNames(#"Filtered Rows"),")","")
Just add one Index column and use UnPivot and Pivot, then what you want appears:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xVRbjsMgDLxK1a9dybL8AuOzVL3/NdZ5EJEmm6r7sx8RMdjDeBh4PO63O3z4PWGpYqBQiNqG1ZITXpHkJKalRgMiGK2XGYYesG9r/jjW3Mtw3o6Q6PY1zH2/5A4sCw38RkzarR4rC4HsirVgay//tCayIfemk50f0KQWILrismYg/U6JwShA95IbEDPGJhPNkpfOjkGtgQZhdIKGei755yb4W5W0kozqlRhLxqUYlp17GUA49WmMVU5iAWmOMvXNSqBUkXttYKlH+GTgu/M3Btc8n3oSKwm2mFkpODtSBxfBelTb2zs7rBnv7JDeEx7toGlGxs0iYyxQ0gazTydXGBgHSsdVjPiPLQLYfIBv2bT0I3TwvOCx3PYOPt326WFh9O2hWR2/Qj9/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
Custom1 = Table.ReplaceValue(#"Unpivoted Other Columns",
each [Value],
each if Text.Contains([Value],")")
then "-" & Text.Replace([Value],")","")
else [Value],
Replacer.ReplaceText,
Table.ColumnNames(#"Unpivoted Other Columns")),
#"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you!
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | Column12 | Column13 |
1,093,968 | 53,976.02 | 53,976.02 | 0 | 1,039,991.48 | 4.93 | |||||||
0 | 0 | 6,964.68 | 0.00 ( | 6,964.68) | 0 | |||||||
150 | 0 | 0 | 0 | 150 | 0 | |||||||
50,250 | 35.88 | 35.88 | 0 | 50,214.12 | 0.07 | |||||||
265,000 | 0 | 0 | 0 | 265,000.00 | 0 | |||||||
1,409,368 | 54,011.90 | 60,976.58 | 0 | 1,348,390.92 | 4.33 | |||||||
285,396 | 0 | 0 | 0 | 285,396.00 | 0 | |||||||
144,075 | 11,481.62 | 11,481.62 | 2,287.23 | 130,306.15 | 9.56 | |||||||
185,750 | 41,730.06 | 41,730.06 | 302.89 | 143,717.05 | 22.63 | |||||||
785,000 | 0 | 0 | 0 | 785,000.00 | 0 | |||||||
1,400,221 | 53,211.68 | 53,211.68 | 2,590.12 | 1,344,419.20 | 3.99 | |||||||
1,400,221 | 53,211.68 | 53,211.68 | 2,590.12 | 1,344,419.20 | 3.99 | |||||||
9,147 | 800.22 | 7,764.90 ( | 2,590.12) | 3,971.72 | 56.58 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
101 | |
94 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |