Forum Discussion

amsrivas's avatar
amsrivas
Helper IV
5 years ago

Power BI Data Modelling

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

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.