Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone. Apologies for the poor title, I'm not quite sure how I could describe it.
I have 2 tables in my model. One has hundreds of numerical columns:
The other one has 3 columns designed to remap the column names in the table above and assign a measuring unit to each column (energy meter data).
I've successfully converted the column names:
I'm including some of these columns in a table and I'd like to have it show as: Column Name, Value and the Unit assigned to that meter.
Through a matrix table I've gotten the first two and in red you can see the desired outcome:
I believe I need to create a Measure to give me a list of Units based on these values, the problem is that I'm not filtering anything, these columns are being dragged and dropped in the visualisation.
Unpivotting the main data and then creating a relationship between the column names isn't an option due to the length and other constraints on the real dataset.
I've put the dummy model used here:
https://drive.google.com/file/d/1UzI-PZFtrRbbjWpnhRRxEbGsiMy4RVjV/view?usp=sharing
Any help would be appreciated,
Thank you
Solved! Go to Solution.
Hi @TomBLG
In the picture you add the Unit to the right of the Value column, however, I am afraid of it is not possible.
The reason is (see bellow) if you put measure into the visual, then it will add a line at the bottom instead of a column on the right.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi Xiaotang,
I agree with you. Without unpivotting the main data to then create a relationship between Unpivotted Attribute and FieldName, it doesn't appear to be doable. I'll be marking this as the solution since it's not a possible request.
I ended up creating a custom column to include the Units in brackets after the field name and this is working just fine.
Hi @TomBLG
In the picture you add the Unit to the right of the Value column, however, I am afraid of it is not possible.
The reason is (see bellow) if you put measure into the visual, then it will add a line at the bottom instead of a column on the right.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi Xiaotang,
I agree with you. Without unpivotting the main data to then create a relationship between Unpivotted Attribute and FieldName, it doesn't appear to be doable. I'll be marking this as the solution since it's not a possible request.
I ended up creating a custom column to include the Units in brackets after the field name and this is working just fine.
Sadly, Power BI Desktop does not support the EVALUATE DAX function.
Can we assume that the UnchangedColumns are the dimension attributes and the renamed columns are the facts? In that case you can unpivot your table, and then blend in the unit of measurement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NVbJkeQwDMtl3vMQKV6KZWvyT2MhQH6525Z4AQT579+PLzs/vz9Vcx9mx2zwo0+24zl54j5t40DrxPtRXfdLm99HnTyF51mx7ucdaxLPjMj7PrubHvzkuh9szV73i8Vpu1dyT1zf5THr+j4n9zWFG3Gfs4exrLI2/IjddS/GGb//d/a17F2KsHOHHNS1M3Py/j/u/G++ze6F6sihPRt+2NZ1Iwh4YohrP9c4oSxggq6HBwYG6HIjPV5YwxB9MhZjWCr02GFhdyJ9BROqa2fQOUyde+L00FcWq4MUnQXe5Qwa1bvHPNa5hnoDEGa/lLVbz30RwFRJTCmHDGJst96sYy/WD0Ge1BUnqB1dJd8s0HTnfZ8WRp8TLfZ4b5EgmW0Xk93TPrLD3MomTCQAloR6HULbKXaE1wtyRfFLGflkEXvTd6twk1mMyYPlipoeFtYedjuTBc60n7/fS3XnB1sMZgN1FjKNhIypfd8jAsUCdvFg5KHl2eABgaFDH+ZmZazZ+VK4AF3zPmRxZ4ltK3weHU+RGwBKkQNzIpdbQKFZdBKxCSlf5FciWzZZbnXCThrvHNpGpGyBnWtGB2qZWmQRkd6b/9GX90KALvRp4jPIY4+vh8kDWKW5ax8hs5XX9lS+ZydvzjE2Uwq4SRdOhy0xNuQxsF9MN0FoMT+n1Dxo4CWmRjCqHa2+avUEeMcWhm9iVmOKbsE9o+q9Rl30Egt0Gpu7FuGOdj7RH4wfDFV7RAzJ23EkiREWytyWOqqHV/Y6rPKcagW+rJXrqPc619MN4VEZPICbhBhMaIXpyY4o36waogzJaZECoAZZDhFlBx5wmF2wklIVoB/Z1R/gfrv90d3YL48r93+IiARebAeznLrU21xoRElnQnVbi3qDzxIqpNAqmLskzEzyAVX2/UpNm0NlqiPBR6iMdfqQANBY5gYrLkTCWTakmgoZXGNnTIvFYAhvXKG5FuDysMeL1YMGUtCgDSxzI4cjqm29PpQjAQ0tZ9ww7gLHOMJqSsfQdK9XRiqnKDDIGOZBczK6LawgrMaCXIUVvFfPNFn8CO9eQdaBbaFyG6l+CXLLe6BmND5sDsggzyF0Eunsp+5rJQ8gqEeg4NC76sYDKPbRQU0kiB3xxFhYAtqS6eR5JtE5Gm6rcgsQd/UGNOsjtHgKFjiTviW1xxsGjvHPgJAoGXpHg3iDca2Ga0oQ5hovzJoH/8h2ax0AkUm4vd/isUV5u9SnJ/g+H9XFlztF+QMbw4jdavYFxaCcwRixRPovsxdeJzl7pks3YIQdh71GL5o6jxglRTUKr+wbF0+sUXLRAJ3z2TJpH4ogebrUWkq6tCBsgoAVheGcp8+YHYQCOLiS77e87HlO4rGypBSFSSDLdkLbUb1t6Yjmrr0AczQ0c6Fq34Yh7YdciZzuI/Ig8rf3aNJDlp9cjIh65VTNo6bGuFGZcrmYAy5x2uVTaKwI16Sv/iwW6ZHFi6gDw8bAKiWKCpA3s6QnjmK+pUHbxWMeaJXUtkGVSRRUkS7R6YRU280FQ5I2Q9eIkBGU1sIE6UXtvR6tXK2SLgXzrb3T/K0kGugGcy9CrTcOjBkIevzBr7Uae6P2x2OppVrKedn6BqCyx1hjb9S+u/Df338=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnchangedCol1 = _t, UnchangedCol2 = _t, UnchangedCol3 = _t, UnchangedCol4 = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, UnchangedCol5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, UnchangedCol6 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t, Column38 = _t, Column39 = _t, Column40 = _t, Column41 = _t, Column42 = _t, Column43 = _t, Column44 = _t, Column45 = _t, Column46 = _t, Column47 = _t, Column48 = _t, Column49 = _t, Column50 = _t, Column51 = _t, Column52 = _t, Column53 = _t, Column54 = _t, Column55 = _t, Column56 = _t, Column57 = _t, Column58 = _t, Column59 = _t, Column60 = _t, Column61 = _t, Column62 = _t, Column63 = _t, Column64 = _t, Column65 = _t, Column66 = _t, Column67 = _t, Column68 = _t, Column69 = _t, Column70 = _t, Column71 = _t, Column72 = _t, Column73 = _t, Column74 = _t, Column75 = _t, Column76 = _t, Column77 = _t, Column78 = _t, Column79 = _t, Column80 = _t, Column81 = _t, Column82 = _t, Column83 = _t, Column84 = _t, Column85 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UnchangedCol1", Int64.Type}, {"UnchangedCol2", Int64.Type}, {"UnchangedCol3", Int64.Type}, {"UnchangedCol4", Int64.Type}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"UnchangedCol5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"UnchangedCol6", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", Int64.Type}, {"Column38", Int64.Type}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", Int64.Type}, {"Column42", Int64.Type}, {"Column43", Int64.Type}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", Int64.Type}, {"Column48", Int64.Type}, {"Column49", Int64.Type}, {"Column50", Int64.Type}, {"Column51", Int64.Type}, {"Column52", Int64.Type}, {"Column53", Int64.Type}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", Int64.Type}, {"Column58", Int64.Type}, {"Column59", Int64.Type}, {"Column60", Int64.Type}, {"Column61", Int64.Type}, {"Column62", Int64.Type}, {"Column63", Int64.Type}, {"Column64", Int64.Type}, {"Column65", Int64.Type}, {"Column66", Int64.Type}, {"Column67", Int64.Type}, {"Column68", Int64.Type}, {"Column69", Int64.Type}, {"Column70", Int64.Type}, {"Column71", Int64.Type}, {"Column72", Int64.Type}, {"Column73", Int64.Type}, {"Column74", Int64.Type}, {"Column75", Int64.Type}, {"Column76", Int64.Type}, {"Column77", Int64.Type}, {"Column78", Int64.Type}, {"Column79", Int64.Type}, {"Column80", Int64.Type}, {"Column81", Int64.Type}, {"Column82", Int64.Type}, {"Column83", Int64.Type}, {"Column84", Int64.Type}, {"Column85", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",Table.ToRows(Table.SelectColumns(FieldAttributes,{"SQL Name", "Description"}))),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"UnchangedCol1", "UnchangedCol2", "UnchangedCol3", "UnchangedCol4", "UnchangedCol5", "UnchangedCol6", "NewName1", "NewName2", "NewName3", "NewName4", "NewName5", "NewName6", "NewName7", "NewName8", "NewName9", "NewName10", "NewName11", "NewName12", "NewName13", "NewName14", "NewName15", "NewName16", "NewName17", "NewName18", "NewName19", "NewName20", "NewName21", "NewName22", "NewName23", "NewName24", "NewName25", "NewName26", "NewName27", "NewName28", "NewName29", "NewName30", "NewName31", "NewName32", "NewName33", "NewName34", "NewName35", "NewName36", "NewName37", "NewName38", "NewName39", "NewName40", "NewName41", "NewName42", "NewName43", "NewName44", "NewName45", "NewName46", "NewName47", "NewName48", "NewName49", "NewName50", "NewName51", "NewName52", "NewName53", "NewName54", "NewName55", "NewName56", "NewName57", "NewName58", "NewName59", "NewName60", "NewName61", "NewName62", "NewName63", "NewName64", "NewName65", "NewName66", "NewName67", "NewName68", "NewName69", "NewName70", "NewName71", "NewName72", "NewName73", "NewName74", "NewName75", "NewName76", "NewName77", "NewName78", "NewName79", "NewName80", "NewName81", "NewName82", "NewName83", "NewName84", "NewName85"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"UnchangedCol6", "UnchangedCol5", "UnchangedCol4", "UnchangedCol3", "UnchangedCol2", "UnchangedCol1"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, FieldAttributes, {"Description"}, "FieldAttributes", JoinKind.LeftOuter),
#"Expanded FieldAttributes" = Table.ExpandTableColumn(#"Merged Queries", "FieldAttributes", {"Unit"}, {"FieldAttributes.Unit"})
in
#"Expanded FieldAttributes"
Hi Ibendlin,
Both unchanged and changed columns are the same type of data. Some of them simply won't require a rename as they come off SQL with the name format we want.
I'm unable to unpivot my data as this is a small part of a much larger report, all of it running on the same model. On top of that, the real model has 3 similar tables, with thousands of rows and hundreds of columns. Unpivotting just one turns the file to 200mb due to its length, this is not an option I'm afraid.
For the time being, I've added another column to the table I'm using for renaming:
= Table.AddColumn(Source, "Name", each [Description]&" ("&[Unit]&")")
This provides me with the name I want and the unit in brackets. It's not ideal as I'll need to rename the field to remove the "(Unit)" part but short of a measure that I'm missing to get Unit done in a way through DAX, it may be the best alternative.
We have few dozen reports, each with multiple pages and graphs built with the main dataset tables not being unpivotted. An enormous amount of work would need to be done in the graphs to have them display the same way in that format. For this exercise, I can't rely on unpivotting Data.
Thanks for having a look, appreciated.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |