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 fellows,
I was helping a coworker while she brought up a question. I will demonstrate it as follows:
A | SalesValue | Address |
12346548 Task1 | 1597823$ | St.1 street |
326597 Task2 | 3258$ | St.3 street |
657415 Task3 | 25478 $ | St.2 street |
And she wants this table to look like:
A | Task | SalesValue | Address |
12346548 | 1 | 1597823$ | St.1street |
326597 | 2 | 3258$ | St.3street |
657415 | 3 | 25478$ | St.2street |
Basically, she tries to add a column and pull all the second lind values (inside same cells not a new row) and list them under a new column with the assigned name like above one.
Note: The preference is using power bi design page without going to Power Query Editor page.
Thanks
Solved! Go to Solution.
Actually it is so easy, Go to :
Format -> Row headers -> Stepped layout
and turn it "Off"
That simple and easy without going over huge Power Query massaging and scripting 😉
Actually it is so easy, Go to :
Format -> Row headers -> Stepped layout
and turn it "Off"
That simple and easy without going over huge Power Query massaging and scripting 😉
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Note that I added a step up front to replace blanks with nulls, which is necessary for this to work.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XctNCoAgEIDRq4i0lGD+1M5RO3HRwlW79P6kYWDCwAzM90LQgMRW2Guj20QT2gbZnEda6rmXFVQud0rl/R5nvmCoCW2N/5xQfLc0W1RDa8UxyB+jsPOqc5w5fXF8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, SalesValue = _t, Address = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"SalesValue", Currency.Type}, {"Address", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"A", "Address"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "ForUnPivot", each Number.RoundUp([Index]/3,0), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ForUnPivot"}, "Attribute", "Value"),
Custom1 = Table.FromColumns({#"Unpivoted Other Columns"[ForUnPivot], #"Unpivoted Other Columns"[Value], List.Repeat({"A", "SalesValue", "Address", "Task"}, Table.RowCount(#"Unpivoted Other Columns")/4)}),
#"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[Column3]), "Column3", "Column2")
in
#"Pivoted Column"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks! Is it possible to do it in DAX or any other way except using Power Editor?
I missed that part of your initial post. This would be very difficult or may not be possible in DAX.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |