学习
实践
活动
工具
TVP
写文章

基于Excel2013的PowerQuery入门

关系图.png 简而言之:获取——>分析——>呈现——>发布 1.数据导入Power Query并进行追加查询 新建一个空的excel文件,在导航栏的POWER QUERY中选择文件——>Excel 关闭并上载2.png 从上图可以看出一店6377行增加到10739行。 2.数据的行列管理及筛选 删除行 打开下载文件的02-数据的行列管理及筛选.xlsx,出现如下图所示。 ? 保留重复项按钮位置.png 只有1次购买记录的客户会被删除,多次购买记录的客户会被保留。 例如一个客户有3次购买记录,保留重复项后该客户被保留3次购买记录。 ? 成功删除错误行.png 7.转置和反转 打开下载文件的07-转置和反转.xlsx,如下图所示。 ? 打开文件图示.png ? 加载数据到PowerQuery.png ? 成功上载结果.png 透视——不要聚合 选择不要聚合这个表,加载至PowerQuery第一步操作如下。 ?

6.3K50

《Python for Excel》读书笔记连载1:为什么为Excel选择Python?

Python和Excel都是很久以前发明的,这并不是它们唯一的共同点:Excel和Python都是一种编程语言。 换句话说,电子表格工具缺乏可靠的应用程序体系结构,并且通常没有文档记录和测试。 数据层访问单元格F3开始的交易汇率表的数据,该表充当这个小应用程序的数据库。 它最初是为了管理Linux源代码而创建的,从那时起,它征服了编程世界,甚至微软在2017年采用Git来管理Windows源代码。 Power Pivot与PowerQuery齐头并进:概念上讲,这是使用PowerQuery获取和清理数据之后的第二步。PowerPivot可帮助你直接在Excel以吸引人的方式分析和显示数据。

38120
  • 广告
    关闭

    2022腾讯全球数字生态大会

    11月30-12月1日,邀您一起“数实创新,产业共进”!

  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    PowerBI PQ 深度套路之构造模板函数

    在文章,很少讲PowerQuery,也就是PowerBI的查询编辑,因为在实际应用,确实没有碰到特别复杂的情况,原因有三: 1.使用PowerBI应减少在导入数据环节的复杂数据转换处理,这会拖慢速度 因此,PowerBIPowerQuery与PowerBI的DAX不同,DAX需要真正写代码,而PowerQuery却可以按套路来操作,尽量不写或少写代码。 电影数据案例来理解模板函数法 在此前的文章:PowerBI 零代码智能网抓中国电影大数据让人惊叹,这里不再重复之前的说明,但从另一个角度来看待这件事,为了获得2008年到2018年的每年电影票房数据 这里最关键的一步就是创建模板函数,如下: ? 这里的巧妙之处在于:参数,创建函数,与后续的调用自定义函数融为一体,是PowerBIPowerQuery的正统操作。 通过模板函数与分治法的结合可以处理很多的场景,因此这不失为一项PowerBIPowerQuery的重要套路。

    82010

    个人永久性免费-Excel催化剂功能第22波-Excel文件类型、密码批量修改,补齐PowerQuery短板

    的原生功能,具体位置在【数据】选项卡内。 多工作薄合并之PowerQuery优势 数据结果输出灵活,可突破100万行记录的限制 PowerQuery作为自助式BI的工具一部分,数据处理完的数据结果,一般用于被PowerPivot数据建模工具所引用 ,Powerpivot对数据的处理能力可突破100万行的Excel表数据,最大的记录数只以用户机的电脑内存有关,内存足够大的64位电脑,装载个1000万行的数据都是小CASE。 、文件夹名、表名等操作,可轻松在PowerQuery的可视化界面完成修改。 同时PowerQuery的数据结果是以数据连接的方式存储具体的数据处理步骤,即只需点击【刷新】按钮,即可重新按原来的数据处理逻辑,重新原始数据读取数据到目标表,此过程无需其他的额外操作即可完成。

    24130

    「数据ETL」数据民工到数据白领蜕变之旅(七)-将Excel(PowerQuery+VBA)的能力嫁接到SSIS

    所以,若可以在标准的SSIS流程引入PowerQuery的轻量化数据处理功能,将原有复杂的数据结构,先进行清洗整合后,变为一个干净的数据源供SSIS调用上传至数据库,这时整个方案的可行性和性价比都非常可观 当次处理一个Excel文件而不是整个文件夹的文件,可以保障性能同时也防止Excel工作表行数不足存储所有数据记录行的报错数据丢失情况。 ://www.jianshu.com/p/cb89929bb8ae 「数据ETL」数据民工到数据白领蜕变之旅(三)-除了Excel催化剂之外PowerQuery值得期待 https://www.jianshu.com /p/d154b09c881d 「数据ETL」数据民工到数据白领蜕变之旅(四)-有了PowerQuery还需要SSIS吗? /p/8de014b1f957 「数据ETL」数据民工到数据白领蜕变之旅(六)-将Python的能力嫁接到SSIS https://www.jianshu.com/p/033342b02dae

    27120

    「数据ETL」数据民工到数据白领蜕变之旅(三)-除了Excel催化剂之外PowerQuery新物种同样值得期待

    在Excel催化剂,对于PowerQuery非常擅长的领域,Excel催化剂就比较少去开发,所以使用Excel催化剂的用户群体,更佳的方式是同时掌握Excel催化剂+PowerQuery,在两者根据自己不同场景 PowerQuery得以实现。 同时PowerQuery上的操作过程,并详细记录为步骤的模块,并且步骤可以在有先后顺序的多个步骤间来回切换,感受步骤的前后对数据源产生的影响,非常好用。 结语 本篇工具的角度,分析在笔者眼中的PowerQuery的功能特性及价值。 * 系列文章 数据民工到数据白领蜕变之旅(一)-工具总览 https://www.jianshu.com/p/2bd3f90206ec 数据民工到数据白领蜕变之旅(二)-重温Excel催化剂经典 https

    21520

    BI-SQL丨Drop & Alter

    但是在数据库仓库,我们往往需要借助Drop和Alter或者其他语句才能对数据仓库进行修改。 为什么在数据仓库我们不能像在PowerQuery中进行同样的操作呢? 其实这个和二者的定位有关。 PowerQuery的定位,是针对PowerView进行服务的,虽然PowerQuery自成体系,可以称之为一个微型的数据仓库,但是一般情况下,我们标准数据仓库拿到的数据都是比较干净的数据。 在数据治理的规范化标准,其中就要求数据仓库必须能够稳定、持续的提供数据源。 列名称 数据类型 2.对表列进行删除: ALTER TABLE 表名称 DROP COLUMN 列名称 3.改变表列的数据类型: ALTER TABLE 表名称 ALTER COLUMN 列名称 UNIQUE(唯一)更新:新增唯一约束: ALTER TABLE 表名称 ADD UNIQUE (列名称) 新增多个列唯一约束: ALTER TABLE 表名称 ADD CONSTRAINT uc_约束名

    8420

    【强强联合】在Power BI 中使用Python(2)

    其实我们仔细看一下场景1和场景2,它们之间是个逆过程,场景1是Python获取数据传递到Power BI,而场景2是Power BI或者Power Query获取了数据,用python来处理。 当然,我们也可以继续在这个表里进行一系列操作,比如复制一张表,再创建一个新dataframe表: ? 运行,得到结果: ? 以上只是在循序渐进地告诉大家,powerquery是可以用Python进行数据清洗的,并且清楚地告诉大家调用Python的方法,大家应该很熟练了吧。 以下才是重点(当然上面也是): ? 在IDE运行无误后复制到powerquery的Python脚本编辑器: ? 点击确定,返回结果: ? 后面两列就是我们想要的手机号和邮箱了。 当然,数据清洗的整个流程是复杂多变的,结合本文所讲的内容,希望大家都能充分挖掘powerquery和Python在数据清洗过程的优缺点,结合起来使用,势必能事半功倍。

    1K31

    「数据ETL」数据民工到数据白领蜕变之旅(四)-有了PowerQuery还需要SSIS吗?

    控制流和数据流的区别,用笔者语言来说是控制流是类似我们写程序的一个函数、过程任务片段,完成一件数据单元的任务,而数据流,是指控制流涉及到数据的转换处理的加工过程,就像一个管道一样,控制流的起点开匝放水 同样地,SSIS已经自动帮我们按源的数据类型和字段名称,生成了SQL语句用来创建目标表(若是已经有现成表,直接选择即可,会将源数据直接插入到目标表存放,怎样避免重复插入及插入数据去重等,就需要一些进阶的用法 同样地转到【映射】选项卡,可以看到SSIS自动帮我们创建好对应的列匹配关系,若源和目标的字段名称不同,需要手动去在输入列与目标列做匹配映射调整。 结语 本篇简略分析了PowerQuery的一些局限性,建议有PowerQuery的使用经验的群体,可以再往前一步,加入到SSIS的阵营来。 ://www.jianshu.com/p/cb89929bb8ae 数据民工到数据白领蜕变之旅(三)-除了Excel催化剂之外PowerQuery新物种同样值得期待 https://www.jianshu.com

    31010

    「Sqlserver」数据分析师有理由爱Sqlserver之七-最刚需的数据导入导出功能-导入篇

    现实场景 在数据消费端,就算是数据分析师的角色,对于正规的公司来说,都不会轻易地开发数据库的访问权限给到终端用户,绝大部分的场景只会是给予导出Excel、csv等文件格式的权限,并且通常来说,导出的记录数也是有限制的 Excel插件提供合并工作薄功能(Excel催化剂也不例外,同样有合并工作薄,不过是基于PowerQuery常规处理困难的一些特殊脏乱的数据的方案,能够使用PowerQuery的优先使用)。 完美的向导操作,帮助我们完成了多个信息的配置 所有数据增量导入到目标表$sheet1 一般来说,SSMS没有及时更新到刚刚我们创建的新表sheet1,需要点击表对象右键刷新下即可显示出来。 若没有权限创建链接服务器,也可使用其他OPENDATASOURCE、OPENROWSET等函数来创建,可能还是会有权限问题,文件要在Sqlserver安装的机器上,如果是共享文件夹路径的方式,方案又复杂许多 、Load数据加载),对于数据导入的功能,也是非常详尽,上述的向导也是最终转换为SSIS包的方式来实现,在SSIS上进行操作,更加灵活、强大,无论是数据源、目标数据存放的选择面都大很多,几乎可以将数据任意源位置移动存放到任意目标位置

    23330

    PQ-数据转换11:隔行删除(保留)数据的巧妙设计

    小勤:大海,我网上复制了个数据好恶心啊,每隔一行就有一些乱七八糟的字符,怎么样能方便删掉? 大海:所以在PowerQuery里对删除行的功能的设计挺巧妙的,可以适应多种情况。我们先来看隔行删除的。 Step-1:获取数据 Step-2:删除间隔行 功能参数说明: 要删除的第一行:即从第几行开始删除数据,本例因为是隔行删除,所以第1行要保留,第2行开始删; 要删除的行数:即每次删几行? 上面选择第2行开始删数据,本例每次只要删1行; 要保留的行数:即每次删了之后,接着的数据保留几行?本例每次删1行留1行。 小勤:嗯。这个图看着比较容易理解。再来个比较复杂的例子说明一下?

    28520

    饕餮盛宴:微软定义BI未来 新PowerBI全解读

    并且使用的技术是 PowerQueryPowerQuery 已经内置在 PowerBI,甚至是Excel,不再需要使用IT才能玩转的SSIS,甚至在家中就可以定义 PowerQuery ,轻松完成企业统一的数据准备 轻松数据获得洞察力 零代码 AI 集成 定制机器学习模块集成 首先是自然语言查询: ? 接着是我们刚刚已经看到过的 AI 集成: ? 以及定制机器学习模块: ? 我们宾馆的住宿记录来进行分析,首先可以提取关键词来分析: ? 可以通过筛选看出那类的宾馆适合怎样的客户: ? 进而对销售的商机进行机器学习分析来进行预测: ? 这里可以对销售机会创建一个机器学习模型: ? 虽然我们不是数据科学家也不会用 R 或 PY 编写任何 AI 算法,但依然可以 PowerBI 中集成的机器学习模块定制自己所需要的,这里选择一个 是/否 判断模型: ?

    55810

    PowerBI 十月多项更新 AI问答及实时报告 颠覆来袭

    页面自动刷新 当使用 DirectQuery 方式连接到数据源时,就可以在页面设置自动刷新的时间间隔,如下: ? 不难看出可以最细设置到 1 秒的级别。先来看看实际的效果吧: ? 这是一种经典的查询下压,将用户的需求,报告层下压到数据源层,而这种查询的下压是通过 DAX 引擎将 DAX 自动翻译为 SQL 的,进而数据源拉取数据。 注意:如果你打开了预览功能,你也看不到页面设置刷新时间间隔是因为你必须在这页使用至少一个基于 DQ 模式的视觉对象。 这是用来诊断 PowerQuery 的查询过程的。如下: ? 这一特性的主要目的是面向IT的,用来诊断 PowerQuery 的查询耗时问题。 使用方法是: 点击【开始诊断】 刷新 PowerQuery 相关查询 点击【停止诊断】 系统会生成两个表:诊断的汇总信息和诊断的详细信息。如下: ?

    36710

    个人永久性免费-Excel催化剂功能第53波-无比期待的合并工作薄功能

    最重要的是连微软官方都提供了此功能,使用PowerQuery的ETL功能,无论易用性还是功能强大性来说,秒杀市面一切的第3方开发的功能。 不是所有的合并功能都是有必要的功能,这一点笔者三翻四次地不断在作强调,Excel催化剂的功能开发过程,可看到是非常有节制性地开发一些真正是刚需的功能。 同时PowerQuery真是难得的好用的数据ETL工具,对于少量数据来说。 以上所示的是常见到的一些数据不规则情况,实际也常发生,对PowerQuery来说,部分场景还可适用,但列名不同需要不同列名重新检验时,大部分插件和PowerQuery都很难支持,以下可看Excel催化剂效果展示 行工作表模拟的是列字段顺序不同,已经被自动处理成相同顺序 第4行因有脏数据原因,识别出来的标题行为数据区域,故出现了数字作为列标题的抓取结果 第5行完成找不到标题,标题行号标示为999999,因抓取标题的过程只遍历前面的记录行数据

    17750

    日历表的使用

    本节有三个知识点,日历表排序,在PowerQuery创建日历表,定制日历表的使用。 1 日历表排序 为什么要排序? 2 日历表的制作方法 关于日历表的制作方法有很多,利用Excel表最简单常用,此外另一个作者本人比较青睐的方法是在PowerQuery中直接建立一张日期表,这样你就不用再担心数据源表的变更问题。 2)在公式栏里输入=List.Dates,输入日期起点、长度、颗粒度(下图演示以2016年1月1日为起点,长度1000天,颗粒度即间隔为1天) 3)再点击“到表"转换成表格式。 当然请你记住这个日期表在数据模型是作为Lookup表使用的,所以要在后续的工作关联好数据表。 3 定制版日历表 上一节我们提到日历表分为两种:标准版和定制版。 比如2015年7月1日到2015年7月31日在定制的财年日历表ID是7,那么我们需要在标准日历表把2015年7月的每一天都标注ID为7,这个工作你可以直接在Excel源表添加。

    71110

    Power BI数据回写SQL Server(2)——存储过程一步到位

    我们可以通过创建一个存储过程来读取PQ生成的文件,然后解析到数据库。 二、JSON篇 第一步,在SQL Server创建一个存储过程,调用json格式的文本为参数; 第二步,powerquery生成JSON格式其实更加简单,使用Json.FromValue(),直接将 这里留给大家一个问题,如果我就想在powerquery显示中文,应该怎么办呢?欢迎大家在留言区交流分享。 好了,关于如何Power BI如何向SQL回写数据,我们用了三篇文章来讲解。 总结起来,方法有这么几个: 1、借助Python的相关库,在PQ调用,以达到回写SQL的目的; 2、在PQ循环按行导入SQL; 3、在SQL创建存储过程,然后在PQ调用存储过程,JSON或XML 文件作为参数 同时,总结了几位朋友的案例,发现应用场景主要集中在这么两个方面: ①pq爬取的数据只是状态数据,转瞬即逝,无法变化记录; ②解决不同数据库之间的壁垒,比如要定期将数据某个数据库备份复制到另一个

    52351

    Power BI数据回写SQL Server(1)没有中间商赚差价

    我们在【重磅来袭】在Power BI 中使用Python(4)——PQ数据导出&写回SQL 讲过如何在Power BI调用Python实现powerquery获取和处理的数据回写到MySQL。 写入MySQL的库是pymysql,而如果要写入SQL SERVER我们需要更换一个库: pip install pymssql 名字上我们也能看出,这两个库的作者是同一个人,因此用法几乎完全一致。 我们先从SQL Server导入一张表到powerquery: ? 我们再来试试Value.NativeQuery方法,是将一条record记录数据直接插入数据库: Value.NativeQuery ( Sql.Database 那么重要的就来了: 如果我们能够将PQ返回的表按行转换为一条条的record记录,再逐条导入SQL Server,那么我们的需求就得到了解决。

    1.2K31

    PQ获取TABLE的单一值作为条件查询MySQL返回数据

    还有,把他上个月销售记录附在这张表后面,发我看看。 问题来了:这个时候如果是你,你会怎么完成这个任务?(前提,数据库中有所有人的全部记录。) 常规思路是,直接在数据库查找这个人,按条件返回即可,只不过还得写一个导出到文件,然后打开文件复制到原来的表,说实话还真有点繁琐。 PowerQuery能帮助我们很简便地解决这个问题。 比如我们要查询的人是moon,那么首先在powerquery编辑器右键moon然后深化: ? 这样就得到了显示的值:moon。注意这里的值是一个单纯的值,而不是一个一行一列的表。 我按销售额大到小的顺序排列,然后在查询结果表中点击右键刷新,发现: ? 诶?出问题了吧,一看查询到的结果,全都是infi的记录,这是怎么回事呢? 我们到查询编辑器中看看: ? 注意第三行: NAME = 更改的类型{1}[NAME], M语言允许我们通过坐标的方式获取表单一的值,[NAME]代表NAME列,而{1}代表第2行,因为表都是标号为0的行开始的。

    31351

    PowerBI系列之什么是PowerBI

    Power BI 简单且快速,能够 Excel电子表格或本地数据库创建图表。 同时Power BI也是可靠的、企业级的,可进行丰富的建模和实时分析,及自定义开发。 Power Query在Excel 通过简化数据发现、访问和合作的操作,增强了商业智能和数据自助服务体验。 安装需求:PowerQuery内置在Excel2016以上版本,在Office2013需要在微软官网下载:https://www.microsoft.com/zh-cn/download/details.aspx id=39379 使用场景:只是需要对原始数据进行处理直接使用PowerQuery就可以满足你的需求。 二、轻量级建模组件PowerPivot简介 微软官方介绍:PowerPivot是一种数据建模技术,用于创建数据模型,建立关系,以及创建计算。

    1.3K20

    这个网站用PowerBI、PowerQuery不好爬?这一招交给你

    accurate&sortTr=f_bbrq_s;desc&gbrqStart=&gbrqEnd=&sxrqStart=&sxrqEnd=&sort=true 5、将简化的链接输入地址栏 返回了10条记录 并且我们在这里发现,该筛选一共有613条记录,每页10条,也就是62页: 我们可以将上方链接替换为62看看,果然这一页上只有2条记录: 二、PowerBI或PowerQuery获取数据 1、创建一个文本参数 2、新建源-web: 将此参数替换掉链接的那个数字5: 3、展开得到数据: 4、创建参数: 在刚刚得到的这个表上右键创建函数: 5、新建一个空查询: 写入= {1..70}获取一个列表,

    16810

    扫码关注腾讯云开发者

    领取腾讯云代金券