Forum Discussion
Microsoft EmployeeFormat on Row, but not columns
Hi t-atgeor,
As shared by Eno1978 and smoupre, data type in Power BI is column based, changing it by rows is not supported.
We could write measure to calculate the data and then format it to the wanted data type.
An idea about data type in measure, check:
From your data example, would it be available to have the Total cost, Total Revenue and Product Amount as column, then add another date column to identify the values. In this way it would be available to format the data.
Date
Total Cost
Total Revenue
Product(Units)
2012/01/01
$10.00
$20.00
3000
2012/02/01
$5.00
$8.00
1000
2012/03/01
$15.00
$12.00
3000
Then regarding the difference and the percentage of difference, we could write measure or a calculated column to calculate.
What smoupre did here could be done under Query Editor, when your data is loaded, click Edit Queries, this opens Query Editor, under Transform tab, there are Transponse and Use first row as headers, when done those, on the Home tab, click Advanced Editor, you would find the code which should be similar with what smoupre posted:
Regards
Microsoft EmployeeHi Anonymous,
thanks for responding. What I would like to do is the following ( please see picture below). I would like to have the Cost and Revenue in USD, while keeping the products sold as units (but NOT dollars). Currently, I can change the format of the whole column, while am looking only for the two rows change.
Is it possible to have this done?
Regards, Atanas
Community ChampionPerhaps something like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM4vLlHSUTI00DMwANKmKJShgYGqUqwOTG1QallqXmkqUMIIqtwCqs4ISptC1QcU5ZemJJcoaJTmZZYUawKljA1gJhqA9cMooPpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Current Month" = _t, #"Previous Month" = _t, Difference = _t, #"Difference %" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Current Month", Int64.Type}, {"Previous Month", Int64.Type}, {"Difference", Int64.Type}, {"Difference %", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
#"Promoted Headers"- t-atgeor
Hi Greg_Deckler,
could you please explain to me how I should activate this function. Am not fully aware where to paste it?
Thanks, Atanas
v-micsh-msftHi t-atgeor,
As shared by Eno1978 and smoupre, data type in Power BI is column based, changing it by rows is not supported.
We could write measure to calculate the data and then format it to the wanted data type.
An idea about data type in measure, check:
From your data example, would it be available to have the Total cost, Total Revenue and Product Amount as column, then add another date column to identify the values. In this way it would be available to format the data.
Date
Total Cost
Total Revenue
Product(Units)
2012/01/01
$10.00
$20.00
3000
2012/02/01
$5.00
$8.00
1000
2012/03/01
$15.00
$12.00
3000
Then regarding the difference and the percentage of difference, we could write measure or a calculated column to calculate.
What smoupre did here could be done under Query Editor, when your data is loaded, click Edit Queries, this opens Query Editor, under Transform tab, there are Transponse and Use first row as headers, when done those, on the Home tab, click Advanced Editor, you would find the code which should be similar with what smoupre posted:
Regards