Forum Discussion
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 terms if i have a table of 12 x 10 i want to convert it into 3 x 40 . is it possible?
Thanks in Advance
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
2 Replies
yes, this can be done easily. Not sure why you need Power BI for this, but it can be done.
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi 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
