Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Need urgent help.
I have data like below:
State | Engineer|
Florida, Johny
Florida, Nick
Indiana, Johny
Indiana, Nick
Florida, Peter
Georgia, Peter
Indiana, Mike
Georgia, Mike
I want a pivot table like
Solved! Go to Solution.
Hi @Greatbi1 ,
Just merge the table with itself and filter the same states out:
the mcode is:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL8pMSVTSUfLKz8irVIrVQRbzy0zOBgt55qVkJuahKkOIwZUhdAaklqQWgcXcU/OL0jNRxRBafTOzU9GUQYRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Engineer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Engineer", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Engineer"}, #"Changed Type", {"Engineer"}, "Changed Type", JoinKind.Inner),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"State"}, {"State.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Custom", each if [State] = [State.1] then 0 else 1),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
And use a matrix like:
Hi,
Use the Matrix table,
Which column you want in value table. Please click on the (down side arrow on value) select "Distinct or count" whichever you require.
Please refer the below print screen for your reference.
Thanks
Hey thanks for answering, I was expecting that first column should be state.
Hi @Greatbi1 ,
Just merge the table with itself and filter the same states out:
the mcode is:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL8pMSVTSUfLKz8irVIrVQRbzy0zOBgt55qVkJuahKkOIwZUhdAaklqQWgcXcU/OL0jNRxRBafTOzU9GUQYRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Engineer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Engineer", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Engineer"}, #"Changed Type", {"Engineer"}, "Changed Type", JoinKind.Inner),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"State"}, {"State.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Custom", each if [State] = [State.1] then 0 else 1),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
And use a matrix like:
Thanks a lot @camargos88 . Awsome thinking. Hats off to you. I had spent more than a day on it. I can relax now. Thanks a ton.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |