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
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. 🙂

      • bblackwell3's avatar
        bblackwell3
        Helper II
        3 years ago

        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?