Forum Discussion

Chaitanya_malir's avatar
Chaitanya_malir
Helper I
3 years ago
Solved

Data Modelling in powerBI desktop

Hey Guys, I am new to powerBI. i have data in a .txtfile like this  I want to extract 3 bytes in a row and another 3 bytes as another row and so on . how can i do it. Basically in simple ter...
Data Protection
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Chaitanya_malir ,

    Here are the steps you can follow：

    1. Select all columns using Ctrl +A – Unpivot Column.

    2. In Power query. Add Column – Index Column – From 1.

    Result：

    3. Create calculated column.

    Mod =
var _mod=
MOD('Table'[Index],3)
return
IF(
    _mod=1,1,0)
    Group =
SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),[Mod])

    4. Create calculated table.

    New Table =
var _table=
SUMMARIZE('Table','Table'[Group],
"1",
MAXX(FILTER('Table','Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])),[Value]),
"2",
MAXX(FILTER('Table',
'Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])+1),[Value]),
"3",
MAXX(FILTER('Table','Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])+2),[Value]))
return
SUMMARIZE(_table,[1],[2],[3])

    5. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  Chaitanya_malir ,

Here are the steps you can follow：

1. Select all columns using Ctrl +A – Unpivot Column.

2. In Power query. Add Column – Index Column – From 1.

Result：

3. Create calculated column.

Mod =
var _mod=
MOD('Table'[Index],3)
return
IF(
    _mod=1,1,0)
Group =
SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),[Mod])

4. Create calculated table.

New Table =
var _table=
SUMMARIZE('Table','Table'[Group],
"1",
MAXX(FILTER('Table','Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])),[Value]),
"2",
MAXX(FILTER('Table',
'Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])+1),[Value]),
"3",
MAXX(FILTER('Table','Table'[Index]=
MINX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Index])+2),[Value]))
return
SUMMARIZE(_table,[1],[2],[3])

5. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly