How to change the structure of a dataset ?

[Anonymous]

Hi,

I have data set for different cases which are on different stages and I am trying to change the structure of the dataset (required outcome).

It is not necessary that stages name are fixed. Any idea on this would be really helpful.

Available Dataset
CaseID	Stage Name	Stage Starting Date
1	A	12.1.2019
2	A	2.2.2019
2	B	11.1.2019
3	C	31.3.2019
4	A	1.4.2019
4	B	15.4.2019
4	C	1.5.2019
5	A	1.6.2019
5	D	1.7.2019
Required Outcome
CaseID	Stage 1	Stage 2	Stage 3	Stage 4	Stage 5
1	A
2	A	B
3	C
4	A	B	C
5	A	D

Best Regards,

ankbaner

[RobbeVL · Accepted solution]

Hi ,

Please check the Advanced code below, this should do the trick:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcxBDoAwCATAv3BuiNCi8ajtL0j//w2RVCse9jLZXVUgSHBYiJGQF9qhJwUeysgRz7tK32o2qZZMmKeW5xZLRD+Qv1avykR592vE5rgN7Bc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CaseID = _t, #"Stage Name" = _t, #"Stage Starting Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage Starting Date", type date}, {"CaseID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Stage Name.1", each "Stage"&" "&[CaseID]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CaseID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Stage Name.1"]), "Stage Name.1", "Stage Name")
in
    #"Pivoted Column"

Let me know if you need help imlementing this in your model.

see liink to example file: 

https://drive.google.com/file/d/13pezcriz3fkX0UAOJKDuAyObJ8IEeV_K/view?usp=sharing 

Regards,

Robbe  

[deeksha]

make different calculated columns with required stage names.