Forum Discussion

bblackwell3's avatar
bblackwell3
Icon for Helper II rankHelper 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
Icon for Solution Sage rankSolution 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
Icon for Helper II rankHelper 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
    Icon for Solution Sage rankSolution Sage
    3 years ago

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

    • bblackwell3's avatar
      bblackwell3
      Icon for Helper II rankHelper 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?

      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage
        3 years ago

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