I am struggling to manipulate a data source that has disparate data fields grouped into single columns and tagged with corresponding unique recordids. Below is an example dataset of what I have coming in:
Fieldvaluetext | fieldname | recordid |
2017 | Year | 1 |
Q1 | Quarter | 1 |
Q12017 Note | Update | 1 |
2017 | Year | 2 |
Q2 | Quarter | 2 |
Q22017 Note | Update | 2 |
2017 | Year | 3 |
Q3 | Quarter | 3 |
Q32017 Note | Update | 3 |
2017 | Year | 4 |
Q4 | Quarter | 4 |
Q42017 Note | Update | 4 |
2018 | Year | 5 |
Q1 | Quarter | 5 |
Q12018 Note | Update | 5 |
2018 | Year | 6 |
Q2 | Quarter | 6 |
Q22018 Note | Update | 6 |
2018 | Year | 7 |
Q3 | Quarter | 7 |
Q32018 Note | Update | 7 |
2018 | Year | 8 |
Q4 | Quarter | 8 |
Q42018 Note | Update | 8 |
I am trying to be able to display and filter based on the different Fieldname categories. An example visualation I would like to have is something simple, as shown below:
Year | Quarter | Update |
2017 | Q1 | Q12017 Note |
2017 | Q2 | Q22017 Note |
2017 | Q3 | Q32017 Note |
2017 | Q4 | Q42017 Note |
2018 | Q1 | Q12018 Note |
2018 | Q2 | Q22018 Note |
2018 | Q3 | Q32018 Note |
2018 | Q4 | Q42018 Note |
I feel like there should be a relativley simple way to address this, but for the life of me I cannot figure out a way to do it. Any help would be greatly appreciated!
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DsMgEATQq1hbuwh/TmHJhQsLUSCFOhFy7m8ShDaIqVjNsq+YEEg+hKOVzpxKfQTFNdAu6rh/Urnyf/j9umyvK9fseD/Tb2jLUZHtQA5KD6EigaLagRqUHkJFAUW3Az0oPYSK7opnxaBeDPfiJ8UAxaJeLPcyKxYoDvXiuJdZcUDxqBfPvcxKXcYb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fieldvaluetext = _t, fieldname = _t, recordid = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fieldvaluetext", type text}, {"fieldname", type text}, {"recordid", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[fieldname]), "fieldname", "Fieldvaluetext")
in
#"Pivoted Column"
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".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DsMgEATQq1hbuwh/TmHJhQsLUSCFOhFy7m8ShDaIqVjNsq+YEEg+hKOVzpxKfQTFNdAu6rh/Urnyf/j9umyvK9fseD/Tb2jLUZHtQA5KD6EigaLagRqUHkJFAUW3Az0oPYSK7opnxaBeDPfiJ8UAxaJeLPcyKxYoDvXiuJdZcUDxqBfPvcxKXcYb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fieldvaluetext = _t, fieldname = _t, recordid = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fieldvaluetext", type text}, {"fieldname", type text}, {"recordid", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[fieldname]), "fieldname", "Fieldvaluetext")
in
#"Pivoted Column"
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".
User | Count |
---|---|
135 | |
58 | |
56 | |
55 | |
48 |
User | Count |
---|---|
125 | |
74 | |
54 | |
52 | |
50 |