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

[sivas07]

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

[dufoq3 · Accepted solution]

Hi @sivas07,

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