Please help us to get required format in power bi

[Anonymous]

Source have three column 1)Template 2)Selection description3)Question full description

Need power bi report in attached format where we have source coming from datamodel and Output is required in Power BI

[v-alq-msft]

Hi, @Anonymous 

Based on my research, It is unavailable to put two columns in the same hierarchy. As a workaround, you may create a calculated column to combine two columns.

calculated column:
Selection description & Question full description = 
Source[Selection description]&"  "&Source[Question full description]

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymousdoable through M this way !!!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCknNLchJLElV0lFySS1OLsosKMnMzzNE5RopxepEKwWCRIMSS4szEvOArMSkZGzCKalp2ITTMzKxCWdl50CEjYCc4MS8rMRKIMPRCYugswsWQVc3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Template", type text}, {"Description1", type text}, {"Description2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Template"}, {{"ad", each _, type table [Template=text, Description1=text, Description2=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
X = [ad],
Y = Table.RemoveColumns(X,{"Template"})
in Y),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    X = Table.RemoveColumns(#"Transposed Table",{"Column2"}),
    Y = Table.RemoveColumns(#"Transposed Table",{"Column1"}),
    Z = Table.RenameColumns(Y,{{"Column2", "Column1"}}),
    Custom1 = X&Z,
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each Number.IsEven([Index])=true),
    #"1" = Table.ExpandTableColumn(#"Filtered Rows", "Column1", {"Description1", "Description2"}, {"Description1", "Description2"}),
    #"2" = Table.SelectRows(#"Added Index", each (Number.IsEven([Index])=false)),
    Custom4 = #"1"&#"2",
    #"Sorted Rows" = Table.Sort(Custom4,{{"Index", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Column1", "Description1", "Description2", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Description1]=null then [Column1] else [Description1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom", "Description2", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"",Replacer.ReplaceValue,{"Description2"})
in
    #"Replaced Value"

[MFelix]

HI @Anonymous ,

You can add the following measure to your model:

Question Full Description Text = CONCATENATEX('Table';'Table'[Question Full Description];UNICHAR(10))

This will give the result below (see attach PBIX file)

If you want to have also the Selection description repeated you can add a mesure similar to the previous one but refering to Selection Description column.

[MFelix · Accepted solution]

Hi @Anonymous,

Use the matrix visual then place on rows the template and selection description in values place the question full description then you just need to drill down to the lowest level of the rows using the buttons on header of the matrix.

[Anonymous]

Thanks Miguel for prompt response.

I am not getting desired result.

Attaching the outcmome of your suggested solution.I am also attaching the required format in power Bi

[amitchandak]

I think the most nearest would be

Refer :https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual#stepped-layout-with-matrix-v...