Forum Discussion

Theeshk93's avatar
Theeshk93
Frequent Visitor
1 year ago
Solved

Need Help on a table

Hi, It will be much appreaciated if someone can help in the below query please. Problem - I'm creating a table with the names of managers and the departments they are incharge of. My source tab...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Theeshk93 

     

    In the query editor, you can unpivot all columns and then remove blank rows from the resulting value column and then add an index or order column for each unique department per manager either with DAX or M. You can use this order column to create a kind of invisible key so provide a row context for each department per manager so all

    Please refer to the attached pbix for the details.

     

SundarRaj's avatar
SundarRaj
Super User
1 year ago

Hi Theeshk93, here's another solution. Thanks

M Code used:

( The Table used here that is the Source is the above table mentioned above by you )

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrNU9JR8srPAFE++aWZxUqxOtFKniFArgIYg7humXmJecmpKGIgVnh+bmpeMYpQQFF+cilQtARFFISDE3NSi1FEYMbGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
List = List.Transform ( Table.ToColumns ( #"Changed Type" ) , each List.Select ( _ , each not ( _ = " " ) ) ),
Table = Table.PromoteHeaders ( Table.FromColumns ( List ) )
in
Table