Power BI Data Modelling

[amsrivas]

Hi Folks!!

I have a data as below

ColA	ColB	ColC
Data	Data	Data
L-1	2	3
L-2	5	6
Cloud	Cloud	Cloud
L-3	8	9
L-4	11	12
Azure	Azure	Azure
L-5	14	15
L-6	17	18

* 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 

NewColumn	ColA	ColB	ColC
Data	L-1	2	3
Data	L-2	5	6
Cloud	L-3	8	9
Cloud	L-4	11	12
Azure	L-5	14	15
Azure	L-6	17	18

Please suggest!!

Thanks

Amit

[amsrivas]

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]

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

Blog: WhatTheFact.bi

[amsrivas]

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

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([Column1],2)<>"L-" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1]=[Custom] then "Remove" else "keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"})
in
    #"Removed Columns"

Hope this helps.

[selimovd]

Hey @amsrivas ,

then you have to find a way to split them and do the separation in portions.

If you have an unlimited number of columns you should change it to a way that is not fixed on specific columns.

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

Blog: WhatTheFact.bi

[amsrivas]

Hi @selimovd 

Thanks for your help!!

However, i have find the solution to handle the scenario when the number of rows against every head are not same.

Please refer below blog for details

https://fascinatingsql.blogspot.com/2021/04/power-bi-split-table-into-multiple.html

Thanks

Amit Srivastava

[amsrivas]

Hi @selimovd ,

Real requirement is to load the data from the source of Azure status and create Power BI report based on that.

When i am loading into Power BI Dataser via Web Connection, i am getting data like as shown below

So here, data from Row 2 to row 13 is belongs to "Compute" and then belongs to "Developer Tools" and so on as shown in Azure status page.

Is there other wayout to retreive the result.

I need to have Power BI Dataset where i can filter data based on "Compute , Developer Tool etc and can see rest of the detail.

Please suggest, if there is any other way to achieve the same.

Thanks

Amit 

[HotChilli]

In Power Query,

Select 'Use first row as headers'.

Add a column which copies the sub-headings and puts null for the details.  You can use the interface and the code will be similar to:

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [ColC] = "Blank" then null else if [ColC] = "Good" then null else if [ColC] = "Bad" then null else [ColC], type text),
 

Then select the new column and do a 'Fill Down'.

Then from the header of the most appropriate column, filter the data to only show rows of Good, blank and whatever else that is not a 'sub-heading'.

good luck

[selimovd]

Hey @amsrivas ,

this should be possible in Power Query with Unpivot or Pivot.

Check the following video for that:

Transpose, pivot or unpivot in Power Query? - YouTube

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

Blog: WhatTheFact.bi