Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Data in a column to be split into diff column's as per below sample data

 I am new in Power BI , have tried Pivoting & Unpivoting but was not able to acheive the required format as this is a large data which needs to be updated on a daily basis in the table ..

Need help to get the Data transformed so that the report can be done ..

 

 

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Anonymous,

     

    Result

     

    v1 (Table.Group)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jUw1TUyMDIBcjzzUjITgXRAUX6KoVKsTrSSEbqS0GBHqAIjsAJjDAXeQMI5PyUVYoAJTjsSi0qMwUpM0ZW4pxblJuZVgsxJLC6B2GOGrsitKDEvORXh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S. no." = _t, Date = _t, Country = _t, Items = _t]),
    GroupedRows = Table.Group(Source, {"Items"}, {{"All", each Table.RenameColumns(_, {"Items", [Items]{0}?}), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"S. no.", Order.Ascending}})
in
    SortedRows

     

    v2 (List.Accumulate)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jUw1TUyMDIBcjzzUjITgXRAUX6KoVKsTrSSEbqS0GBHqAIjsAJjDAXeQMI5PyUVYoAJTjsSi0qMwUpM0ZW4pxblJuZVgsxJLC6B2GOGrsitKDEvORXh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S. no." = _t, Date = _t, Country = _t, Items = _t]),
    ItemsList = List.Distinct(Source[Items]),
    Ad_ItemColumns = List.Accumulate(
        ItemsList,
        Source,
        (s,c)=> Table.AddColumn(s, c, each if [Items] = c then c else null, type text)
    )
in
    Ad_ItemColumns

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Anonymous,

     

    Result

     

    v1 (Table.Group)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jUw1TUyMDIBcjzzUjITgXRAUX6KoVKsTrSSEbqS0GBHqAIjsAJjDAXeQMI5PyUVYoAJTjsSi0qMwUpM0ZW4pxblJuZVgsxJLC6B2GOGrsitKDEvORXh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S. no." = _t, Date = _t, Country = _t, Items = _t]),
    GroupedRows = Table.Group(Source, {"Items"}, {{"All", each Table.RenameColumns(_, {"Items", [Items]{0}?}), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"S. no.", Order.Ascending}})
in
    SortedRows

     

    v2 (List.Accumulate)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jUw1TUyMDIBcjzzUjITgXRAUX6KoVKsTrSSEbqS0GBHqAIjsAJjDAXeQMI5PyUVYoAJTjsSi0qMwUpM0ZW4pxblJuZVgsxJLC6B2GOGrsitKDEvORXh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S. no." = _t, Date = _t, Country = _t, Items = _t]),
    ItemsList = List.Distinct(Source[Items]),
    Ad_ItemColumns = List.Accumulate(
        ItemsList,
        Source,
        (s,c)=> Table.AddColumn(s, c, each if [Items] = c then c else null, type text)
    )
in
    Ad_ItemColumns