Change Sequenced data to Columns

[bblackwell3]

Hey All. I have a PowerBI model that has a table that contains Ids of applications, platform and the platform sequence. The platform sequence refers to which platform the app will migrate to over time. Dummy data below

However, I need this information for the platform migration in columns, based on the sequence number like the below

I thought of possibily using Tranpose, but it doesn't seem to be the answer.  How would I approach doing this, and avoiding duplicates?

[bolfri]

Try this one.

let
    Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc3V9JRcgwPBpKGSrE60UoW5kZAtruzG1zE0twYpKaqtCgVLmZoYmAA5PgXJSbnIEQhpplDNBshGeeSWJLonFgEF4UYGVKUWQAXgpoYHBkBF4IYBxExRjINbqsxsvvAfjBGNss3OAQuBDEL4jITJLPc/INC4EIQkyDaTJBN8nN1hAjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Platform = _t, #"Platform Sequence" = _t]),
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"ID", Int64.Type}, {"Platform", type text}, {"Platform Sequence",  type text}}),
    #"Dodano kolumnę niestandardową" = Table.AddColumn(#"Zmieniono typ", "ColumnName", each "Platform " & [Platform Sequence]),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Dodano kolumnę niestandardową",{"Platform Sequence"}),
    #"Kolumna przestawna" = Table.Pivot(#"Usunięto kolumny", List.Distinct(#"Usunięto kolumny"[ColumnName]), "ColumnName", "Platform")
in
    #"Kolumna przestawna"

Results:

[bblackwell3]

@bolfri This looks like it may work. However, I cannot clearly understand some of Polish language exactly

[bolfri]

I am so sorry. Here is the English version:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc3V9JRcgwPBpKGSrE60UoW5kZAtruzG1zE0twYpKaqtCgVLmZoYmAA5PgXJSbnIEQhpplDNBshGeeSWJLonFgEF4UYGVKUWQAXgpoYHBkBF4IYBxExRjINbqsxsvvAfjBGNss3OAQuBDEL4jITJLPc/INC4EIQkyDaTJBN8nN1hAjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Platform = _t, #"Platform Sequence" = _t]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Platform", type text}, {"Platform Sequence",  type text}}),
    AddColumnName = Table.AddColumn(#"Change Type", "ColumnName", each "Platform " & [Platform Sequence]),
    DeleteColumns = Table.RemoveColumns(AddColumnName,{"Platform Sequence"}),
    Table.Pivot = Table.Pivot(DeleteColumns, List.Distinct(DeleteColumns[ColumnName]), "ColumnName", "Platform")
in
    Table.Pivot

[bblackwell3]

Thank you! This syntax is almost exactly what I need. How would I incorporate the change of the source to a sql. database and schema? Like the below?

Source = Sql.Database("SERVERNAME", "DATABASE NAME"),

    Roadmap_AppTargetPlatform = Source{[Schema="Roadmap",Item="AppTargetPlatform"]}[Data],

[bolfri]

let
Source = Sql.Database("SERVERNAME", "DATABASE NAME"),
Roadmap_AppTargetPlatform = Source{[Schema="Roadmap",Item="AppTargetPlatform"]}[Data],
#"Change Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Platform", type text}, {"Platform Sequence", type text}}),
AddColumnName = Table.AddColumn(#"Change Type", "ColumnName", each "Platform " & [Platform Sequence]),
DeleteColumns = Table.RemoveColumns(AddColumnName,{"Platform Sequence"}),
Table.Pivot = Table.Pivot(DeleteColumns, List.Distinct(DeleteColumns[ColumnName]), "ColumnName", "Platform")
in
Table.Pivot

[bblackwell3]

I created a new table using the above syntax and altered it to include my actual column names into the syntax below

let
Source = Sql.Database("LOUSQLWTS711", "AppJourneyTrackingDB_Dev"),
Roadmap_AppTargetPlatform = Source{[Schema="Roadmap",Item="AppTargetPlatform"]}[Data],
#"Change Type" = Table.TransformColumnTypes(Source,{{"AppId", type text}, {"PlatformName", type text}, {"PlatformSequence", type text}}),
AddColumnName = Table.AddColumn(#"Change Type", "ColumnName", each "PlatformName " & [PlatformSequence]),
DeleteColumns = Table.RemoveColumns(AddColumnName,{"PlatformSequence"}),
Table.Pivot = Table.Pivot(DeleteColumns, List.Distinct(DeleteColumns[ColumnName]), "ColumnName", "PlatformName")
in
Table.Pivot

However, I get an error?

[bolfri]

It seems that in your table there is no AppId column. 🙂

[bblackwell3]

I thought the same 😄 initially. This is the table I am basing it off of and it has AppId. I also get the error when changing to ID or Id??

 Should I create a blank query for the syntax or create table?

[bolfri]

Crate a table from your SQL connection, then give me a code from advanced editor. I will apply rest of the steps for you.

[bblackwell3]

let
Source = Sql.Database("LOUSQLWTS711", "AppJourneyTrackingDB_Dev"),
Roadmap_AppTargetPlatform = Source{[Schema="Roadmap",Item="AppTargetPlatform"]}[Data]

in
Source

[bolfri]

Give me also a colum names, because this is an issue here.

[bblackwell3]

I still have no resolution for this 😞

[bblackwell3]

Did you need any other info?

[bblackwell3]

I need Id, AppId, PlatformName, and PlatformSequence

[Shaurya]

Hi @bblackwell3,

Transpose is not required in your case. Go to Power Query and Pivot the Platform Sequence column and in the dialogue box, select Platform as Value Column.

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[bblackwell3]

Thanks @Shaurya . That did get me one step closer but..

However, I need for the App ID to only appear one time and 1, 2, 3 and 4 should be populated with the Platform Name. Like the below

[themistoklis]

@bblackwell3 

The pivot parameters should be the ones as shown in the image below.

Make sure the Platform Sequence column is selected first before you click the Pivot Column button.

I have also attached the PowerBI workspace