Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I have a scenario that i'm not sure if it's possible or not. I have a table with an identifier, codetype, and three value columns. See below:
ActivityID | CodeType | ValueNumber | ValueText | ValueDate |
1 | Total | 43 | null | null |
1 | Size | null | Small | null |
1 | OrderDate | null | null | 2024-06-01 |
2 | Size | null | Large | null |
2 | OrderDate | null | null | 2024-06-06 |
3 | OrderDate | null | null | 2024-06-12 |
The column ValueNumber stores the data as type number, ValueText as type text, and ValueDate as type date. In each row one of the three values will have a value and the other two will be blank/null. I want to combine the three columns and then pivot table (Table.Pivot) so it looks like this:
ActivityID | Total | Size | OrderDate |
1 | 43 | Small | 2024-06-01 |
2 | null | Large | 2024-06-06 |
3 | null | null | 2024-06-12 |
Is there a way to have the "Total" column remain as data type number, "Size" as data type text, and "OrderDate" as data type date? For background information, my dataset is much larger as this is a small example; my actual data has 20+ codetypes and thousands of rows. I want an automated way of changing the data type prior to the data showing up in PowerBI. The reason I need the datatypes is because my PowerBI may use measures that requires comparisons or calculations for columns that are data type text or date.
Other than manually changing data type in PowerBI or Power Query, is there a way to maintain data type after combining columns and pivoting the table?
Solved! Go to Solution.
let
Source = your_table,
merge = Table.CombineColumns(Source, {"ValueNumber", "ValueText", "ValueDate"}, (x) => List.RemoveNulls(x){0}, "x"),
types = List.Zip(
Table.ToColumns(
Table.TransformColumns(
Table.Distinct(merge, "CodeType")[[CodeType], [x]],
{"x", Value.Type}
)
)
),
pvt = Table.Pivot(merge, List.Distinct(merge[CodeType]), "CodeType", "x"),
col_types = Table.TransformColumnTypes(pvt, types)
in
col_types
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJL0nMAdImxkAirzQnB0bF6kAUBGdWpSKkgnMTMZX4F6WkFrkklqSiGaGjZGRgZKJrYKZrYAhWbIRhnk9iUXoqsnlGxJlnBlZsTJRiQyOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActivityID = _t, CodeType = _t, ValueNumber = _t, ValueText = _t, ValueDate = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ActivityID", "CodeType"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ActivityID", "CodeType", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[CodeType]), "CodeType", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Total", Int64.Type}, {"OrderDate", type date}})
in
#"Changed Type"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Hi Ibendlin,
I don't want to explicitly change the column type as a step as I want this to be a robust solution. If I have 20+ codetype that can change based on the project, then I will need to alter the code every other day
Thanks,
let
Source = your_table,
merge = Table.CombineColumns(Source, {"ValueNumber", "ValueText", "ValueDate"}, (x) => List.RemoveNulls(x){0}, "x"),
types = List.Zip(
Table.ToColumns(
Table.TransformColumns(
Table.Distinct(merge, "CodeType")[[CodeType], [x]],
{"x", Value.Type}
)
)
),
pvt = Table.Pivot(merge, List.Distinct(merge[CodeType]), "CodeType", "x"),
col_types = Table.TransformColumnTypes(pvt, types)
in
col_types
You can have a mapping table that indicates the desired column type for each column name. For example "ValueText" would map to "type text".
User | Count |
---|---|
20 | |
10 | |
10 | |
9 | |
7 |