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

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
      • 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],