Forum Discussion

amsrivas's avatar
amsrivas
Helper IV
5 years ago

Power BI Data Modelling

Hi Folks!!

I have a data as below

 

ColAColBColC
DataDataData
L-123
L-256
CloudCloudCloud
L-389
L-41112
AzureAzureAzure
L-51415
L-61718

* here L-1 and L-2 data belongs to "Data", L-3, L-4 belongs to "CLOUD" and L-5,L-6 belongs to "Azure", so i need data as below 

NewColumnColAColBColC
DataL-123
DataL-256
CloudL-389
CloudL-41112
AzureL-51415
AzureL-61718

 

Please suggest!!

Thanks

Amit

Need Help

9 Replies

  • amsrivas's avatar
    amsrivas
    Helper IV
    5 years ago

    Hi selimovd 

    I can understand this can be done via Pivot/UnPivot however i am not able to avhieve this.

    Can you help me out by step by step on how to perform above transformation !!!

     

    Thanks

    Amit

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      Hey amsrivas ,

       

      you need 2 items, a function for the transformation and the query itself.

      Add the following query in Power Query with the name "TransformTable":

      (x) as table =>
let
    #"Save Header Name" = {x{0}[ColA]},
    #"Add New Column" = Table.AddColumn(x, "Source", each #"Save Header Name"{0} ),
    #"Removed Top Rows" = Table.Skip(#"Add New Column",1),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Top Rows",{"Source", "ColA", "ColB", "ColC"})
in
    #"Reordered Columns"

       

      And then the query itself:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVTSQaFidaKVfHQNgVwjIDaG8kFsUyA2A/Odc/JLU4A8VBqi0hjItwBiSyjfBMg2BBlnaAQWcawqLUoFclFpiFqQDYZgDaZQETMQxxxEWCjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}}),
    #"Split Tables" = Table.Split( #"Changed Type", 3 ),
    #"Transform Table" = List.Transform(#"Split Tables", TransformTable),
    #"Load Table" = Table.Combine( #"Transform Table" )
in
    #"Load Table"

       

      If you need any help please let me know.
      If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
       
      Best regards
      Denis
       
      • amsrivas's avatar
        amsrivas
        Helper IV
        5 years ago

        HI selimovd ,

        Solution looks promising and its works with data provided.

        However data which i have provided is dummy one and in actual data it is not necessary that we always have two rows between every heads, for e..g data could be any thing like as below

         

        ColA        ColB         ColC
        Data         Data         Data
        L-1             2              3
        L-2             5              6
        L-3            22            3322
        L-4            231          233
        Cloud       Cloud      Cloud
        L-3             8               9
        L-4            11             12
        L-6            78             121
        Azure       Azure        Azure
        L-5            14             15
        L-6            17              18

         

        - It is not necessary that we have only three heads such as Data, Cloud, Azure etc, it could be any number.

        - Number of columns are not fixed are also not fixed, it could be ColA, ColB, ColC, ColD etc, again it could be any number.

        - Number of rows between two heads can be any, it not neccessarily two, in above data set, there are 4 rows for Data, 3 rows for Cloud etc.

         

        Please suggest!!

        Thanks

        Amit