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 tim...
Need Help
Tips and Tricks
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
bblackwell3's avatar
bblackwell3
Helper II
3 years ago

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

      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's avatar
        bolfri
        Solution Sage
        3 years ago

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