Data allignment

[vinb4u2]

Hi Team,

I have data muliple app data like below

AppId , App server  OS                                      DB Server OS                                 Web server OS
APP0006 WINDOWS SERVER 2019
APP0006                                                  Windows SERVER 2012 R2 STANDARD
APP0006                                                                                                          WINDOWS SERVER 2016

and  I need to format like below

AppId                  App server OS                                 DB Server OS                                            Web server OS
APP0006,  WINDOWS SERVER 2012 R2 STAND     WINDOWS SERVER 2012 R2 STANDARD    WINDOWS SERVER 2019

[vinb4u2]

Hi Jianbo

Thanks for your reply and its working fine with single record, some time i got two servers for each app like below 

AppId                  App server OS                                 DB Server OS                                            Web server OS
APP0006,  WINDOWS SERVER 2012 R2 STAND     WINDOWS SERVER 2012 R2 STANDARD    WINDOWS SERVER 2019

APP0006                                                            Windows SERVER 2012 R2 STANDARD
APP0006  WINDOWS SERVER 2016                                                                          WINDOWS SERVER 2016 

and i need format  out put like

AppId                  App server OS                                 DB Server OS                                            Web server OS
APP0006,  WINDOWS SERVER 2012 R2 STAND     WINDOWS SERVER 2012 R2 STANDARD    WINDOWS SERVER 2019

APP0006  WINDOWS SERVER 2016 

Please suggest

[v-jianboli-msft]

Hi @vinb4u2 ,

Why is there only WINDOWS SERVER 2019 and not WINDOWS SERVER 2016 in your results under Web server OS, am I misunderstanding? Can you please explain in detail how the results should be generated?

Best Regards,

Jianbo Li

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

[vinb4u2]

Sorry. Typo. used table format below give more clarity 

App id	App server  OS	DB Server OS	Web server OS
APP0006	WINDOWS SERVER 2019
APP0006		Windows SERVER 2012 R2 STANDARD
APP0006			WINDOWS SERVER 2016
APP0006	WINDOWS SERVER 2016

Output shd be

App id	App server  OS	DB Server OS	Web server OS
APP0006	WINDOWS SERVER 2019	Windows SERVER 2012 R2 STANDARD	WINDOWS SERVER 2016
APP0006	WINDOWS SERVER 2016

[v-jianboli-msft]

Hi @vinb4u2 ,

Please try:

Unpivot the columns:

Then Pivot the columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwIMDAwMFPSUQr39HPxDw9WCHYNCnMNUjAyMLQEioJQrA6yOpDSzLyU/PJiJKVGCkFGCsEhjn4ujkEuWPVgtcFMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AppId = _t, #"App server OS" = _t, #"DB Server OS" = _t, #"Web server OS" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AppId", type text}, {"App server OS", type text}, {"DB Server OS", type text}, {"Web server OS", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"AppId"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Max)
in
    #"Pivoted Column"

Final output:

Best Regards,

Jianbo Li

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