Forum Discussion

bblackwell3's avatar
bblackwell3
Helper II
3 years ago

Change Sequenced data to Columns

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?

Need Help
Tips and Tricks

17 Replies

    • bblackwell3's avatar
      bblackwell3
      Helper II
      3 years ago

      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's avatar
        themistoklis
        Community Champion
        3 years ago

        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

         

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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's avatar
      bblackwell3
      Helper II
      3 years ago

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

      • bolfri's avatar
        bolfri
        Solution Sage
        3 years ago

        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