Table visualization layout problem

[Kien]

I want to put patient code and date in multiple columns. PBi keeps join it to 1 columns. How can I change this setting?

Thank you very much.

[Irwan]

hello @Kien 

looks like 'PatientCode, Year, Quarter, Month, Day' is in one column in your original data.

If you want to show them in multiple column, perhaps you want to split them first in power query.

then, merge it to what you want (i.e merge day-month-year into 'Date' column).

After that, you can plot your data into multiple column.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrA0MDYwMrewNNFRMDIwApKBJUUKRjoKjgVFmTk6CoamSrE6eJT5JlYCFZngV+RVmpcKVGWEXxXURmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient Code, Year, Quarter, Month, Day" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient Code, Year, Quarter, Month, Day", type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([#"Patient Code, Year, Quarter, Month, Day"], ","), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text Between Delimiters", each Text.BetweenDelimiters([#"Patient Code, Year, Quarter, Month, Day"], ",", ","), type text),
#"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([#"Patient Code, Year, Quarter, Month, Day"], ",", ",", 1, 0), type text),
#"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Text Between Delimiters.2", each Text.BetweenDelimiters([#"Patient Code, Year, Quarter, Month, Day"], ",", ",", 2, 0), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Between Delimiters2", "Text After Delimiter", each Text.AfterDelimiter([#"Patient Code, Year, Quarter, Month, Day"], ",", 3), type text),
#"Merged Columns" = Table.CombineColumns(#"Inserted Text After Delimiter",{"Text After Delimiter", "Text Between Delimiters.2", "Text Between Delimiters"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
#"Changed Type1"

Hope this will help you.

Thank you.

[Kien]

Thank you for helping me. Let me add more detail.

My data has columns: patient code, date hierachy, other data,... I put patient code and date as filter on table visualization to do some measures (when use matrix, they will be row value, but i want to use table). in normal view, patient code and date hierachy are in separated columns:

But when in "show as a table" view, and when export to excel,  it will be joined like this (the top of picture is normal view):

do PBi allow me export data to excel with normal layout? how can i change the setting?